ExcelBanter - VLOOKUP

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - VLOOKUP (https://www.excelbanter.com/excel-worksheet-functions/86396-vlookup.html)

kelly

VLOOKUP

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?

Ron Coderre

VLOOKUP

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

"kelly" wrote:

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?

All times are GMT +1. The time now is 11:31 PM.