how do I return a value between two other values from a table

P

P Forbes

Col A Col B Col C
Row1 $1,000.00 $2,500.00 10%
Row2 $2,501.00 $5,000.00 12%
Row3 $5,001.00 $10,000.00 15%
Row4 $10,001.00 $15,000.00 18%
Row5 $15,001.00 $20,000.00 21%
Row6 $20,001.00 $99,999.00 24%

Here's what I have.
I need to return the percentage for a value greater than or equal to Col A
and less than or equal to column B. For example, if the lookup amount is
$9,001, the percentage should be 15%. I'm not looking for an exact match,
but a range between column A & B that will return the value in column C. Any
help is greatly appreciated!
 
L

L. Howard Kittle

With your dollar amounts in column E and the percentage in F and the lookup
value in A1 try this.

=VLOOKUP(A1,E1:F,2,1)

HTH
Regards,
Howard
 
L

L. Howard Kittle

Woops, to clarify... only use the dollar amounts of the upper value. So it
would be 2500, 10000, 15000, 20000, 99999 in column E. Disregard the the
lower limits.

Regards,
Howard
 
T

T. Valko

You had it right the first time! *Do use* the lower limits.

E1 = 9001

=VLOOKUP(E1,A1:C6,3)

Result = 15%
 
L

L. Howard Kittle

Hi Biff,

Could you send me a worksheet with your solution, I'm a bit confused with
your solution... albeit from a pro I know it's on target.

Seems mine worked with the upper limits in my test...?

Thanks,
Howard
[email protected]
 
Top