vlookup formula

[AJB]

if my table is as follows:

1 - 1499 0.170
1500-2499 0.153
2500-3999 0.124
4000-5999 0.107
6000-9999 0.094
10000-14999 0.080
15000-19999 0.068

20,000 0.064

If my value is 4100, how can vlookup return the value 0.107?

thanks,

Andy

 

[Dave]

Hi,
VLOOKUP only needs the lower value for each criteria. It gets its upper
value from the next cell in the table. Your data should be just:

1..................0.17
1500...........0.153
2500...........0.124
4000...........0107
6000...........0.094
10000.........0.080
15000.........0.068
20000.........0.064

If your table is A10:B17 and your number (in this case, 4100) is in A1, put
this into A2:
=VLOOKUP(A1,A10:B17,2)

Regards - Dave.

 

[Gord Dibben]

In-cell LOOKUP

=LOOKUP(B1,{0,1500,2500,4000,6000,10000,15000,20000},{0.17,0.153,0.124,0.107,0.94,0.08,0.68,0.064})


Gord Dibben MS Excel MVP