Excel: Use VLOOKUP to Find Value That Falls Between Range

You can use the VLOOKUP function in Excel with TRUE as the last argument to look up a value that falls in a range and return the corresponding value in another range.

Note that the VLOOKUP 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, you can look up a value that falls in a range.

The following example shows how to do so in practice.

Example: Use VLOOKUP to Find Value that Falls Between Range

Suppose we have the following dataset in Excel that shows the minimum sales needed by an employee at a company to earn a certain bonus amount:

Here is how to interpret this dataset:

If an employee has 0 < 500 sales, they earn $0 as a bonus.
If an employee has 500 < 1,000 sales, they earn $25 as a bonus.
If an employee has 1,000 < 2,000 sales, they earn $50 as a bonus.

And so on.

Suppose we would like to use the VLOOKUP function to look up the value of 870 in the Sales column and return the corresponding amount in the Bonus column.

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

=VLOOKUP(E1, A2:B7, 2, TRUE)

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

Since the exact value of 870 didn’t exist in the Sales column, the VLOOKUP function looked for the next largest value in the Sales column that was less than 870.

The formula identified the value of 500 and returned the bonus value that corresponded to this value, which was 25.

Excel use VLOOKUP to find value that falls between range

Note that the values in the Sales 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.