Vlookup, Offset, Index or Match

[gooniegirl180]

Hi all,

I cannot work this one out and the clock is ticking...Table as below:

ColA Col B C D E F G
15 25 50 75 99 2000
10000 .2 .4 .6 .8 1.0 1.2
17500 .4 .6 .8 1.0 1.2 1.4
38000 .6 .8 1.0 1.2 1.4 1.6
75000 .8 1.0 1.2 1.4 1.6 1.8


If I want to lookup 40,000 on column A, and 26 on row 1, what formula to a put in for it to pick up the value in cell D5 (i.e 1.2)?

Thanks,
gooniegirl180

 

[Richard Schollar]

Hi

You need to have the lower bounds of each range in A column and row 1 then you can use INDEX?MATCH:

=INDEX($B$2:$G$5,MATCH(40000,$A$2:$A$5,TRUE),MATCH(26,$B$1:$G$1,TRUE))

Replace the hardcoded values (40000, 26) with cell references holding these values.

 

[gooniegirl180]

Thank you Richard, that does it!

Regards,
gooniegirl180