A lot of people know how to use the VLOOKUP function to do an exact match – That is to search for something specific and return its value from a table by setting [Range Lookup] argument (input) to FALSE(Exact Match), and they probably wonder about what setting it to TRUE does.
Well, In this article, we are going to explain what the TRUE option does!
VLOOKUP with approximate match – also known as the sorted list version can be used to get the nearest match to our lookup value from our lookup table.
A classic example is getting the commission rates for Salesperson based on their Sales.
Check out the example below:
On the above example, we’ve got some salespersons, and their sales amount, and we need to calculate their commission %, and commission amount.
The syntax for VLOOKUP is:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)]
In Plain English, it would read:
VLOOKUP(find this value, in that table, return the value in column x of the table)
Notice that I haven’t mentioned the range_lookup argument (Input).
The range_lookup can accept one of two values either TRUE or FALSE.
when you do an exact match, you set it to FALSE, but when you need to do an approximate match (use it with a sorted list) as in the case of this article, you can either set it to TRUE or leave it blank.
In this article, I will set it to TRUE, just because I like to explicitly state the values of my arguments!
Anyways, back to our example, The formula in cell C2 reads as:
And the result is 6%.
Here’s how you can think about it to understand it easily:
Excel will search for the lookup value in the lookup table, and when it bumps into the value greater than the lookup value, it will just go back one cell upwards and take that value.
In our example, excel scanned the lookup table ($H$2:$J$10) for the lookup value (B2 = 4215) in the first column to the left (Column H) and when it bumped into the value greater than the lookup value ($5,001), it took the commission rate in column J corresponding to the next best (Second best) value in the table ($4,001), which is 6%.
And then, we can simply get the commission amount, by multiplying the commission% by the total sales (Column D).
There are a few things to note, though:
1- The commission lookup table must be arranged in ascending order. Otherwise, you will get errors.
2- Make sure you don’t have any duplicates in your commission lookup table. For example, if in cell H7, you had $3001 again, and our lookup value is still the value in cell B2 ($4215). then Excel would still bump into $5001 (cell H8) and go up one cell and take the commission rate for the 2nd best cell (H7), and yield a commission rate of 6%.
You can find the example file for the above article Here
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 and receive Excel tips & tricks + Exclusive Udemy discounts to your inbox. You will also get our Excel Shortcuts cheat sheet for free!
- Learn all about the new XLOOKUP Function in Excel! - 2019-09-27
- FOR EACH Loop in Excel VBA - 2018-12-15