Table of Contents
VLOOKUP is a function in Google Sheets that allows users to search for a value within a specific range of data and return a corresponding value from another column. This can be useful for organizing and analyzing large sets of data, as it eliminates the need for manual searching and matching. The user can specify the range of data to search, the column to return the value from, and the criteria to match the value against. This function can save time and increase efficiency in data analysis and management.
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:
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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: