## PROBLEM

There are 10 Sales Representatives.

They all are eligible for a commission which is basis their performance.

For example, Darren Powers is entitled to 10% Bonus since his sales is in the 0-5000 range.

So his Bonus = 10% * 1000 = 100

Total Earning = 1100

## solution 1

Using nested IF statement would be very complicated here, as the formula will look like this!

=IF(C6<5001,C6*(1+$H$6),IF(C6<20001,C6*(1+$H$7),IF(C6<35001,C6*(1+$H$8),IF(C6<38001,C6*(1+$H$9),C6*(1+$H$10)))))

These are the results:

## solution 2

Using VLOOKUP with Approximate Match will reduce this effort significantly.

First, we create the minimum Sales amount required for each Bonus tier.

This is written in Column F, in cells F6:F10.

Now we can write a VLOOKUP function:

=C6*(1+VLOOKUP(C6,$F$6:$H$10,3,TRUE))

We get the same results:

## SOLUTION 3

[ADVANCED BONUS TIP]

You can get the same results even WITHOUT the lookup table – by directly creating the Table Array inside the function itself.

The benefit here is that the function is NOT dependent on any external data.

The Formula used is:

=C6*(1+VLOOKUP(C6,{0,0.1;5001,0.15;20001,0.2;35001,0.25;38001,0.3},2,TRUE))

We get the same results:

GET SAMPLE FILE AND SOLUTION

Send an email at: