# Excel help required

• 21st December 2011, 09:50 AM
JoeBloggs
Excel help required
Annual Bonus Rate 3% 3% £100,000.00
4% £115,000.00
5% £125,000.00
6% £140,000.00
Annual Banked Target £150,000 7% £150,000.00

When someone puts a figure in box B5 (banked target) I want it to change the percentage to whatever it should be according to the right. So less than £100k = 0%, £100k-£114,999.99 = 3% and so on
• 21st December 2011, 10:05 AM
CESIL
Try this =IF(B5>=100000,VLOOKUP(B5,D1:E5,2,TRUE),0)
• 21st December 2011, 10:05 AM
Steve21
If you don't mind it being a bit long-winded

=IF(B5>=F5,E5,IF(B5>=F4,E4,IF(B5>=F3,E3,IF(B5>=F2,E2,IF(B5>=F1,E1,0)))))

But could do it fancy with look-ups etc

Steve
• 21st December 2011, 10:13 AM
pcstru
Use vlookup. Put the %'s in column G (at the other side of the £ figures). The % can then be looked up by putting =vlookup(B5,F1:G5,2,TRUE) in cell B1. The TRUE param tells vlookup to make an inexact match, it will match to the closest figure below whatever you put in B5. So £114,000 will match to 3%, anything above £150K will match to G5 and anything below £100K will return N/A.
• 21st December 2011, 11:12 AM
JoeBloggs
thanks guys got it working :)