formula to return a $ amount for a range of %

[michelledean via OfficeKB.com]

I am currently working on bonus items and my criteria is as follows:

Percentage Bonus amount
78.0% or more $4,000
76.0% - 77.99% $3,000
74.0% - 75.99% $2,000
72.0% - 73.99% $1,000
71.99% or less $0.00

Cell D in excel has the % and cell E will need to return the dollar amount...
can you provide a formula that would return an amount for the corresponding
bonus amount? Help is much appreciated.

 

[T. Valko]

Try this:

=(MATCH(D1,{0,0.72,0.74,0.76,0.78})-1)*1000

 

[Max]

Another way using VLOOKUP

Assume lookup percentages in D2 down

In E2:
=VLOOKUP(D2,{0,0;0.72,1000;0.74,2000;0.76,3000;0.78,4000},2)
Copy E2 down