Bottom line: Learn how to use the VLOOKUP function to find the closest match by setting the last argument to TRUE.
Skill level: Intermediate
Do you have a common activity you do when you stay home sick from work or school? For me, it always included watching an episode of The Price is Right with Bob Barker.
If you have never seen it, The Price is Right is a game show in the U.S. where contestants have to guess the prices of various household items. For the Showcase game, the contestant must guess a price that is equal to or less than the actual retail price of the items to win the game.
If the contestant could use a vlookup formula to make their guess, they would win every time! The contestant would have to set the last argument in the vlookup to TRUE for this to work.
When you start using vlookup you typically set the last arguement [range lookup] to FALSE. This tells vlookup to find an exact match for the text or number you are looking for. Checkout my article on VLOOKUP Explained at Starbucks for more info.
Closest Match with VLOOKUP (TRUE)
Setting the last argument to TRUE tells VLOOKUP to find the closest match to the text or number you are looking for. However, there is a caveat to this “closest match”…
The VLOOKUP starts at the top of the range you specify and looks down (vertically) in each cell to find the value you are looking for (lookup value). It stops searching when it finds a value that is greater than or equal to the lookup value.
In the screencast above the vlookup formula is looking for the number 14 in the list. Since 14 is not in the list, it returns 10. But why???
Well, there are three possible outcomes for vlookup when the last argument is TRUE. The results may seem confusing at first, but they can actually be quite useful.
You can download the file to follow along.
VLOOKUP Closest Match Example.xlsx (13.2 KB)
Outcome #1: VLOOKUP Finds Exact Match
If vlookup finds a value that is equal to the lookup value then it returns a result from that same row.
This is essentially the same as a normal vlookup where you set the last argument to FALSE. The function finds an exact match and returns that row as a result.
Outcome #2: VLOOKUP Finds Closest Match
If vlookup finds a value that is greater than the lookup value, then it returns a result from the previous row.
This means that the “closest match” is going to be less than or equal to the lookup value.
It's also important to note that if the lookup value is greater than the last cell in the lookup range, then the result will be the value in the last cell.
For example, if the lookup value was 30 in the example above, vlookup would return cell B7 (25) as the result. Since vlookup has no more values to look at, it stops at the last cell.
Outcome #3: VLOOKUP Returns an #N/A Error
If vlookup finds a value that is less than the first cell in the lookup range, then it returns an error.
We probably spend a lot of time figuring out why vlookup formulas are not working, and returning errors. This is no exception when using vlookup to find the closest match.
If you are looking up numbers, then the first row in the lookup range needs to be the minimum amount you will potentially lookup. I explain more about this in the example below.
Important: Sort the Lookup Range
Most of the time you will want the numbers in the first column of the lookup range sorted from smallest to largest (A-Z).
The “V” in VLOOKUP stands for vertical. As I explained above, it starts at the top cell in the lookup range and works its way down. As soon as it finds a cell greater than or equal to the lookup value, IT STOPS!
So if you have values at the bottom of the list that are smaller, vlookup will never find them.
Example: Calculating Commissions with VLOOKUP
One of the best examples of this technique is calculating commissions. Please see my article on calculating commissions with vlookup for a full explanation on this technique.
The Price is Right for VLOOKUP's Closest Match
So this is why it would be great for The Price is Right contestants to have a vlookup at their disposal. If they try to guess a price that is too high, vlookup would return the closest match that is equal to the actual retail price. I know it's a stretch, but think about it next time your writing a vlookup formula. 🙂
The host of the show, Bob Barker, always used to have the same closing statement at the end of the show about getting your pets neutered. I think he had something against puppies and kittens… j/k
In closing this post, I will remind you to always sort the first column in your lookup range when finding the closest match with vlookup.
Please leave a comment below with any questions. Thanks!