VLOOKUP

K

kelly

I need to calculate commissions on this sliding scale:
0-94.9% -2%
95%-99.9% 0%
100%-104.9% 1%
105% PLUS 2%
How can I set this up with vlookup?
 
R

Ron Coderre

Try something like this:

With this table set up in A1:B4

Ref Col_A Col_B
Row_1 0% -2%
Row_2 95% 0%
Row_3 100% 1%
Row_4 105% 2%

C1: (a percentage to be looked up)
D1: =VLOOKUP(C1,$A$1:$B$4,2,1)

note: format all value as percents

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Top