vlookup: source is specific and table is a range
 

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

vlookup: source is specific and table is a range
 

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.

"AJB" wrote in message
...
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

vlookup: source is specific and table is a range
 

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

"Alan" wrote:

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.

"AJB" wrote in message
...
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