A classic case is using VLOOKUP to find a commission rate based on a sales number.
Let’s take a look.
I’ll call it “commission_table”.
This will make our VLOOKUP formula a lot easier to read and copy.
Now let’s use VLOOKUP to get the first commission rate for Applebee.
The table is our named range “commission_table”.
For column, we need to provide a number for the column that contains the commission rate.
In this case, that’s the number “2”.
Finally, we need to enter a value forrange_lookup.
If set to “0” or FALSE, VLOOKUP will require an exact match.
When I enter the formula, we get a commission rate of 6%.
If we check the table, this is correct.
From $125,000 to $175,000, the commission rate is 6%.
Now I can copy down the formula to get a commission rate for the remaining salespeople.
Since we have a commission rate, I can also add a formula that calculates the actual commission.
When I re-sort the table in ascending order, the VLOOKUP formulas work again.
