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

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.


"AJB" wrote:

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

In-cell LOOKUP

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


Gord Dibben MS Excel MVP

On Wed, 30 Apr 2008 18:11:34 -0700, AJB wrote:

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