Back to Vlookup Menu

The Vlookup function - closest match


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 you have the following worksheet, and the table is named “sales_table”:

A table for using vlookup function with closest match

=vlookup(B10,sales_table,2,true)
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 doesn’t 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).