In my previous Excel VLOOKUP formula tutorial I mentioned 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.
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.
Note: If you haven’t read the first tutorial, then I recommend you first watch the video below from the beginning to get an understanding of how VLOOKUP works.
Excel VLOOKUP Formula Video Tutorial
Download the Workbook
Enter your email address below to download the sample workbook.
Excel VLOOKUP - Approximate Match on a Sorted List
First let’s set the scene:
In the image below we want to lookup the Commission Rates table in cells G6:I13, and find the rate in column I based on the sales values in column D, and return the result to column E.
Excel VLOOKUP Function syntax:
=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 the nth column of the table, find an exact match if you can, but if not, find the next lowest match)
Note: with the Sorted List version we want Excel to find the next closest option in our table, i.e. an approximate match. To specify this we can leave the 'range_lookup' argument blank, or enter TRUE, or 1.
Excel VLOOKUP approximate match formula example:
Remember we want Excel to find the Commission % Rate and enter it in cell E6, so in English our formula will read:
=VLOOKUP(find where the Sales amount $3,112, falls in the Commission Rates table G6:I13, return the value in column 3 of the table, if there isn't an exact match, find the next closest value)
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 Sales 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 G6:I13, 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 consecutive 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 G11, 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.
Check out my previous tutorial for VLOOKUP Rules & Common Mistakes!