Using Vlookup for intervals

[Guest]

Don't think I was clear on my last question. Here is the table:
Bonus
0%-22.5% 7200
22.6%-23.8% 6600
23.9%-25% 6000
25.1%-26.3% 4800
26.4%-27.5% 3600
As you can see it is a range, not just one number. If our % is 26.9 what
function do I use to calculate the bonus payoff? I know the answer is 3600,
but how do I get excel to calculate it?

 

[Guest]

Set table in columns A & B as below:

0% 7200
22.60% 6600
23.90% 6000
25.10% 4800
26.40% 3600

to get your value for % in C1:

=VLOOKUP(C1,$A$1:$A$5,2)

HTH

 

[T. Valko]

See my response to your other post. If you're entering the lookup_value of
26.9 as a PERCENTAGE, 26.9%, then you also need to format the left hand
column of your table as PERCENTAGE. Use the *LOWER* boundary of each
interval in your table:

0............7200
22.6.......6600
23.9.......6000
25.1.......4800
26.4.......3600

Any lookup_value >= 26.4 will return 3600.

Biff

 

[Guest]

Try this:

=LOOKUP(A1,{0,0.226,0.239,0.251,0.264},{7200,6600,6000,4800,3600})