vlookup: source is specific and table is a range

  • Thread starter Thread starter AJB
  • Start date Start date
A

AJB

my template calculates a specific value, ie 1,357 or 12,789. The rates I
apply to these values are based on the range that the value falls into, ie

0 < 1,500 0.145
1.5<2.5M 0.130
2.5 < 4M 0.106
4M < 6M 0.089
6M < 10M 0.077
10M < 15M 0.065
15M < Avg. 0.054
20M < Open 0.051

I can't get a vlookup to work without using the lowest and highest value of
each rate range:
1 0.170
1499 0.170
1500 0.153
2499 0.153
2,500 0.124
3999 0.124
4,000 0.107
5999 0.107
6,000 0.094
9999 0.094
10,000 0.080
14999 0.080
15,000 0.068
19999 0.068
20,000 0.064

How can I write my vlookup formula (or other applicable formula) to read my
table as shown in my first example.

Thanks much,

Andy
 
Are you using the fourth argument of VLOOKUP? ie
=VLOOKUP(A1,B1:B1000,2,FALSE)
FALSE gives an exact match, TRUE or the forth argument omitted gives the
nearest match.
Regards,
Alan.
 
specifically, I cannot get '3,456 to match up to the range 2,500 - 4,000
without displaying
2,500 : 0.124
3999 : 0.124

I am trying to eliminate visual confusion by using one label for each rate
size:
2,500 - 4,000 : 0.124

Sorry if this is not very clear, thanks for your assistance Alan,

Andy
 
Back
Top