Using the vlookup function (closest match)

When should you use closest match?

In the previous video ("Vlookup with exact match"), we've seen how to retrieve Jake's score, by looking it up in the accompanying table. But what if his score is between the scores mentioned in that table, for example, 74?
That's where "Closest Match" comes into action. Excel will find the value 70 as the closest match to 74 (vlookup will always find the lowest closest match!), and retrieve the letter C from the second column.

Have a look at the video - actually all you have to change is the word "false" to "true". This will tell the computer - "In case you don't find the value in the table, you can use a smaller existing one".

Using the "Closest Match" option is not useful when looking up ID numbers or names, but when you are dealing with values that fall into some range, for example dates, currency, age groups or scores, then you have almost no other choice.

Building the function step by step:

This is exactly the same process as building the vlookup with exact match, but with the following difference: Instead of writing FALSE at the end of the function, write TRUE.

Example:
Let’s assume we have the following worksheet, and the table is named “sales_table”:
And we typed the following function: The function in words: find me the value of cell B10 inside the first column of sales_table, and retrieve me the value next to it from the table’s second column. If you don’t find the value, relate to the closest smaller value you can find.

Hence, the value 14 does'nt appear on the first column of the table, so the function will relate to the value 10 which is the closest smaller value, and retrieve the word “medium” from the second column.

If the value of cell B10 was changed to 90, the vlookup would return us the word “Great” (the closest smaller match it finds would be 50).