2 step LOOKUP? Vector, array? Help.

[SCW]

Need Help!

I've got a Data spreadsheet looking like the following:


Start | End | Class | 1 | 2 | 3 | ... thru to | 150 |
1 | 5 | 1 | 12.25 | 13.50 | 10.00 | ... assorted #s unde
above column headings |
7 | 7 | 2 | 9.85 | 15.00 | 11.20 | ... assorted #s unde
above column headings |
9 | 12 | 3 | 12.00 | 15.25 | 20.00 | ... assorted #s unde
above column headings |
and so on...

My goal is to first find a match contained between "Start" and "End
column values on this Data sheet (above) by entering a desired numbe
(on different Master sheet). This is relatively easy (usin
VLOOKUP)...
I.E.
- Example 1) Enter 4 on Master sheet. On Data sheet - 4 is betwee
the Start value of 1 and End value of 5 - from the first row of dat
from above Data sheet. Answer is Class 1.
- Example 2) Enter 12 on Master sheet. On Data sheet - 12 i
included/between in the End value of row 3. Answer is Class 3.

Also entered on the Master sheet is a desired number for columns heade
1 thru 150... based on this number and a number that is found during th
VLOOKUP (within the Start and End columns)... I want to extract th
corresponding number under the Cloumns headed 1 - 150.

I.E.
- Since the first example above was a value of 4 found between 1 and
in Row 1 (Class 1), and if the desired column value of 3 is also given
the answer I want back is 10.00.
- In the 2nd example, if 12 and 2 are given, I need to get back 15.25

And so on...

Can anyone enlighten me on how to pull this off?

Thanks :

 

[Frank Kabel]

Hi
try
=INDEX('data'!A1:AX100,MATCH(4,'data'!A1:A100,1),MATCH(3,A1:AX1,0))

and replace 4 and 3 with some cell references