Business Problem
Take the following Bonus table:
| Min Sales | Max Sales | Bonus |
| 0 | 500 | 0% |
| 500 | 2000 | 3% |
| 2000 | 20000 | 5% |
| 20000 | 100000 | 7% |
We want to use that information to automatically find the Employee Bonus based on monthly sales figures in a table such as this:
| EmployeeID | SalesAmount |
| 1 | 200 |
| 2 | 550 |
| 3 | 14500 |
| 4 | 7000 |
Business Solution
Now this is a bit tricky because we can't simply search for the SalesAmount in the Bonus table to return the Bonus % because we won't find any matches, so we need to use an approximate search.
VLOOKUP Function Arguments
Let's first review the Function Arguments for VLOOKUP:
Lookup_value - What is the matching number or value between the two tables?
- This should be taken from the same table where you are authoring the formula (don't do the lookup work for Excel).
- This should be a RELATIVE cell reference.
Table_array - What is the name/reference of the table where your answer or additional information is stored?
- This is the other table (NOT the one where you are authoring the formula). Also called the Lookup Table.
- This should be an ABSOLUTE cell reference. (Table names are absolute by default.)
- The first column of this table MUST be the column containing the Lookup_value, Excel will simply find the matching row.
- Sort this table - Excel will always take the first answer it finds.
Col_index_num - Which number column has the answer?
- Take the first column (which contains the Lookup_value) as 1.
- Count the number of columns until you get to the column containing the answer.
Range_lookup - What type of match should be returned?
- TRUE - indicates an approximate match.
- FALSE - indicates an exact match.
VLOOKUP Formula
So, to calculate the Employee Bonus above we would use the following formula:
EmployeeBonus = VLOOKUP([@SalesAmount],Bonus,2,TRUE)
What is this formula doing?
Let's start with EmployeeID 1. They had monthly SalesAmount of $200, so we'll search the first column of the Bonus table for 200. As soon as we find a value >200, we'll stop and go back to the previous row.
500 is the first value that's >200, so we need to stop and go back to the row before 500. That puts us in the first row and returns a 0% bonus.
EmployeeID 2 had $550 monthly SalesAmount. 500<550, so we can keep going. 2000 is the first value that's >550, so we'll stop there and go back to the previous row with a bonus of 3%.
| EmployeeID | SalesAmount | Employee Bonus |
| 1 | 200 | 0% |
| 2 | 550 | 3% |
| 3 | 14500 | 5% |
| 4 | 7000 | 5% |
The trick to getting this to work is that your Lookup Table, in this case our Bonus table, MUST be sorted properly first.
Sort Order Matters
If we try the same formula with the Bonus table out of sort order, we'll get different results:
| Min Sales | Max Sales | Bonus |
| 0 | 500 | 0% |
| 2000 | 20000 | 5% |
| 20000 | 100000 | 7% |
| 500 | 2000 | 3% |
| EmployeeID | Sales Amount | Bonus |
| 1 | 200 | 0% |
| 2 | 550 | 0% |
| 3 | 14500 | 5% |
| 4 | 7000 | 5% |
So what's happening here?
Start with EmployeeID 1 - $200 < 2000 so we'll go back to row 1 and return 0%.
EmployeeID 2 - $550 < 2000 so we'll again go back to row 1 and return 0%. We never get the chance to look at row 4 with 500 and 3% to see that it is a closer match. Excel returns the first match.
EmployeeID 3 - 14500 < 20000 so we'll go up one row to 5%. In this case we happen to have found the correct answer by coincidence.
Sort order matters!
