Google Sheets: Use VLOOKUP to Find Value That Falls Between Range


You can use the VLOOKUP function in Google Sheets 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 Google Sheets 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:

Google Sheets VLOOKUP find value between range

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.

It’s important to 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.

Note: You can find the complete documentation for the VLOOKUP function in Google Sheets here.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

Google Sheets: How to Use VLOOKUP to Return Max Value
Google Sheets: Use VLOOKUP to Sum Multiple Rows
Google Sheets: How to Use VLOOKUP with COUNTIF
Google Sheets: How to Use VLOOKUP with IF Statement

Leave a Reply

Your email address will not be published. Required fields are marked *