Using Vlookup for intervals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

UnisourceforNPH - 12 Jun 2007 20:23 GMT

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?

Reply to this Message

Toppers - 12 Jun 2007 20:30 GMT

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

> Don't think I was clear on my last question. Here is the table:
> Bonus
[quoted text clipped - 6 lines]
> function do I use to calculate the bonus payoff? I know the answer is 3600,
> but how do I get excel to calculate it?

Reply to this Message

T. Valko - 12 Jun 2007 20:36 GMT

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

> Don't think I was clear on my last question. Here is the table:
> Bonus
[quoted text clipped - 7 lines]
> 3600,
> but how do I get excel to calculate it?

Reply to this Message

Teethless mama - 12 Jun 2007 20:43 GMT

Try this:

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

Reply to this Message