commission lookup

[Pat Rice]

I have a commision form that return the amount of commission my sales guys
are suppose to get base on percentage of sales value. a represents the high
of the range while b represents the low end of range. c represent the
percentage for that range and d represents the presentage this saleguy got
for this sales. How do I get e to show the value of 9 which is the
percentage for this sale. Thanks for all you help


a b c d e
100.00% 99.991% 10 98.72
99.99% 97.50% 9
97.49% 95.00% 8
94.99% 92.50% 7
92.49% 90.00% 6
89.99% 87.50% 5
87.49% 85.00% 4
84.49% 82.50% 3
82.99% 0.00% 2

 

[Mike H]

Hi,

If your prepared to sort your table like this with column 2 ascending then
this formula works

=VLOOKUP(D1,B1:C9,2,TRUE)

82.99% 0.00% 2
84.49% 82.50% 3
87.49% 85.00% 4
89.99% 87.50% 5
92.49% 90.00% 6
94.99% 92.50% 7
97.49% 95.00% 8
99.99% 97.50% 9
100.00% 99.99% 10

Mike

 

[TomPl]

Or leave it as is and use this formula:
=IF($D1>$B$1,10,IF($D1>$B$2,9,IF($D1>$B$3,8,IF($D1>$B$4,7,IF($D1>$B$5,6,IF($D1>$B$6,5,IF($D1>$B$7,4,IF($D1>$B$8,3,2))))))))

 

[Pat Rice]

Mike,

That did it, can you tell me why? What does 2 represent? I get true but and
had tried the formula but didn't have the 2 in it. I am new to this and just
winging.

 

[Mike H]

Pat,

=VLOOKUP(D1,B1:C9,2,TRUE)

The formula doesn't use column 1 of your data, it used columns B&C (B1:C9)
Vlookup always looks up the left hand column (B or column 1 in this case) and
returns the column you specify which in this case is 2 or column C. Obvioulsy
the column you specify must be in the range. Specifying column 3 of a 2
coulmn range doesn't work.

When you use the TRUE switch the data must be sorted.

It's actually quite refreshing that someone has asked 'Why'. Often posters
don't and become equally stuck the next time they attempt a similar formula.

Mike

 

[Pat Rice]

Well, I am not one of those people, I like learning and will print out this
post so I don't have to ask again. Thanks again for all your help. You have
made my day brighter.