In my previous Excel VLOOKUP tutorial I told you that there are two ways you can use a VLOOKUP but most people know one way or the other, and only a few know both.
If you haven’t read the first article then read it first to get an understanding of how a VLOOKUP works.
As promised here’s the second way to use it, and I call it the Sorted List version as it relies on the data in the table you are referencing being sorted.
Excel VLOOKUP - Sorted List
First let’s set the scene:
In the list below we want to calculate a commission in column E for each builder. Our commission percentage is based on where the ‘Total $k’ figure falls into the ranges in our table in columns G-I.
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
And to translate it into English it would read:
VLOOKUP(find this value, in that table, return the value in column x of the table)
You’ll notice I haven’t translated the last part of the formula ‘range_lookup’. Unlike the VLOOKUP Exact Match version where we put ‘False’ in this position, with the Sorted List version we want Excel to find the next best option in our table, and so we leave ‘range_lookup’ blank.
Let’s make it even clearer by applying it to row 3 in our example:
Remember we want Excel to find the Commission % Rate and enter it in cell E3, so in English our formula will read:
VLOOKUP(find where Total $k amount $3,112, falls in the Commission Rates table G3:I10, return the value in column 3 of the table)
And to enter it in our spreadsheet our formula in column E for the above example would be:
Let me clarify some points:
1) ‘find where Total $k amount $3,112, falls in the Commission Rates table’ - Excel doesn’t actually take into consideration column H in our table. I have simply put it there to help understand the commission ranges. Excel is in fact looking for the exact amount $3,112 in our Commission Rates table, and when it can’t find it, it finds the next best lower amount and returns the value in column 3.
2) ‘Return the value in column 3 of the table’ is referring to the column number in the table G3:I10, not the column number of the spreadsheet. The information we want returned is the percentage rate, and it is in the third column of the Commission Rates table.
3) If we had duplicates in our Commission Rates table Excel will find the last instance of the value and return the result in column 3. For example, if instead of the amount $4001 in cell G8, you had $3001 again. Excel would return the value of 6% as it’s finding the last best match for our amount. The tip here is to remove any duplicates or you’ll end up with erroneous results.
4) Unlike the VLOOKUP Exact Match version of the formula, this version requires the list to be sorted in ascending order. Just like with duplicates explained above, if it’s not sorted you will end up with erroneous results.
You’ll notice in the formula bar above there are ‘$’ signs around the reference to the table. This is called an absolute reference and it allows us to copy the formula down column E without Excel dynamically updating the table range as we copy.
How can we make this formula even better?
As with our Exact Match example let’s assume the end result of our example exercise is to calculate the commission $ amount. Again we can do this in one step in column E.
Let’s say our commission is calculated as Total $k x Commission %, our formula in cell E3 would read:
Hopefully now you can begin to appreciate why the VLOOKUP is my favourite formula. The concept behind it is pretty basic but it has amazing POWER. Especially when you team it up with other formulas like IF statements, SUMIF and so on.
Enter your email address below to download the sample workbook.
Check out my previous tutorial for VLOOKUP Rules & Common Mistakes!
Did you find this useful? Why not share the love and tell your friends on LinkedIn, Google+, Twitter and Facebook.
Want more tips like this? Sign up for our newsletter below and receive weekly tips & tricks to your inbox, plus you'll get our 100 Excel Tips & Tricks e-book.