Excel: How to Use VLOOKUP with Approximate Match

You can use the VLOOKUP function in Excel to look up a specific value in a range and return the corresponding value in another range.

This function uses the following basic syntax:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

where:

lookup_value: The value to look up
table_array: The range of cells to look in
col_index_num: The column number that contains the return value
range_lookup: TRUE = look for approximate match, FALSE = look for exact match

By using a value of TRUE for the last argument in the VLOOKUP function, you can look for an approximate match of the lookup value.

If an exact match can’t be found of the lookup value, the approximate match is the next largest value in the range that is less than your lookup value.

In order for the correct approximate match to be found, the first column in the range is assumed to be sorted either numerically or alphabetically.

The following example shows how to use the VLOOKUP function in Excel to look for an approximate match.

Example: Use VLOOKUP with Approximate Match in Excel

Suppose we have the following dataset in Excel that contains information about various basketball players:

Suppose we would like to use the VLOOKUP function to look up the value of 28 in the points column and return the corresponding team name.

We can type the following formula into cell E2 to do so:

=VLOOKUP(F1, A1:C11, 3, TRUE)

The following screenshot shows how to use this formula in practice:

The formula finds the value 28 in the points column of the dataset and returns the corresponding value in the team column, which is Blazers.

Since an exact match was found for the lookup value, there was no need to search for an approximate match.

However, suppose we look up the value 19 in the points column, which is a value that does not exist:

Since a value of 19 didn’t exist in the points column, the VLOOKUP function looked for the next largest value in the points column that was less than 19.

The formula identified the value of 17 and returned the team name that corresponded to this value, which was the Warriors.

Excel VLOOKUP with approximate match

Note that the values in the points column are already sorted from least to greatest, which is why the VLOOKUP function was able to correctly identify the next largest value that was less than the lookup value.

If the first column in your lookup range is not sorted, the VLOOKUP function could return unexpected results.