Using VLOOKUP, you can retrieve values from a specific row in a table by performing a vertical lookup. Here’s how to use it in Google Sheets.
You often do a vertical lookup search in real-life situations, such as looking at a restaurant menu. You’ll look at the corresponding price in another column to the menu items. The VLOOKUP Sheets function works similarly, as it retrieves data from one column and shows the matching data in the corresponding cell.
If that’s a little confusing, don’t worry. This article explains the VLOOKUP function in detail, including its syntax and how to apply it to your spreadsheets. Read on to master this part of Google Sheets.
What Is VLOOKUP in Google Sheets?
VLOOKUP is a version of the LOOKUP formula. The “V” stands for vertical. This formula can perform a search in the first column in a set of specified data. But, the lookup parameters have to be in the first column.
We use the VLOOKUP function for vertical tables when the search parameter is in the first column, and you wish to look at data in a specific number of columns.
How Is VLOOKUP Different From HLOOKUP?
HLOOKUP in Google Sheets finds data in a horizontal table. However, VLOOKUP finds data in a vertical table. Most of us have data in our spreadsheets in a vertical form, which is why VLOOKUP is much more useful than HLOOKUP. Other than that, the parameters used in the formulas are the same, so if you’re aware of how HLOOKUP works, then you know how to use VLOOKUP. The vice versa is true as well.
On a related note, if you’re familiar with XLOOKUP in Excel, using both HLOOKUP and VLOOKUP in Google Sheets should be easy.
Things to Know About the VLOOKUP Formula
Here are a few things you need to be aware of before using the VLOOKUP formula in your spreadsheet:
- The VLOOKUP formula doesn’t look towards the left. It only searches in the first column of the range. If you’re looking to perform a left lookup, use the INDEX and MATCH formulas instead. You can add AND/OR parameters to the search with this combo.
- The VLOOKUP formula is case-insensitive in Google Sheets, meaning it will not distinguish between uppercase and lowercase characters.
- If the VLOOKUP formula returns wrong results, ensure the is-sorted parameter is set to FALSE. This should fix the output.
- When the is-sorted parameter is set as TRUE, remember that the first column in the range should be in ascending order.
- VLOOKUP can search to find a partial match using the operators represented by question marks (?) or asterisks (*).
Syntax for VLOOKUP
The VLOOKUP function has four parameters, three of which are necessary, while one is optional. Here is the syntax for the function:
=VLOOKUP(key, range, index, is-sorted)
This is what each of the parameters used in the VLOOKUP formula do:
- key: the value the function uses to search the other column
- range: specifies the cell ranges to look through to find the data.
- index: specifies which column to search. The first column is represented as 1. The value must be between 1 and the total number of columns.
- is-sorted: an optional argument to specify whether the range is sorted or not. If left empty, the value is TRUE by default. Or, you can type FALSE if the data you have is not sorted.
Examples of VLOOKUP in Google Sheets
VLOOKUP can be a bit confusing to execute due to some of its nuances. Here are a few examples of the formula in action to better understand the function.
Performing a Simple VLOOKUP
The simplest way to use the VLOOKUP function is by referencing a cell in the first parameter and the range in the second to find a cell that matches the reference provided by the user.
In the below example, we will pretend we need to find the sales data for Employee 7. Although it’s easy to see the answer in such a small spreadsheet, you can imagine that with thousands of rows of data, performing a simple VLOOKUP would save a ton of time.
Here’s how to perform the search with the VLOOKUP Google Sheets function:
- Click an appropriate empty cell.
- Type the first part of the function, which is =VLOOKUP(
- Enter the first argument. In this example, the cell, with Employee No. 7, is A8.
- Type a comma
- Enter the next argument, to define the cell range. In the above example, the cell range is A1:C11.
- Type another comma.
- Enter the argument that defines which column to search. In this example, it is column 3.
- Press Enter.
Nested Function in VLOOKUP
Google Sheets allows you to use a function inside another formula. This is called nesting. In this example, we wish to look for the employee that made the least amount of sales.
Finding that requires the use of the MIN function in Google Sheets. We will write the MIN function inside the VLOOKUP formula to do this. Here are the steps you need to follow to do this:
- Click on an appropriate empty cell.
- Type the first part of the formula, =VLOOKUP(
- Use the MIN function as the first argument by typing MIN(. You don’t have to add an equals sign to a nested formula.
- Enter the range that should be checked to find the minimum value. In the above example, it is A2:A11.
- Type a closing bracket for the MIN formula.
- Type a comma.
- Enter the cell range for the formula. In this example, the range is A1:C11.
- Type another comma.
- Add the column index number to search with. In the above example, it is 3.
- For the last argument, type FALSE as the data we have is not sorted.
- Type a closing bracket for the formula.
- Press Enter to perform the search.
Keep On Searching With the VLOOKUP Function in Google Sheets
The VLOOKUP function is extremely useful, but sometimes can’t perform the exact search you’re looking for. There are plenty of other search functions in Google Sheets, and even more across the full range of Google Apps. Keep learning more, and you’ll be a Google Apps pro in no time.
All Rights Reserved for Andrew Carter