Photo: Tada Images / Shutterstock.com
Google Sheets is a highly adaptable tool suitable for various data management and analysis tasks, ranging from basic calculations to intricate data manipulation. Among its numerous power features, VLOOKUP is particularly vital for those working with extensive datasets.
Working with VLOOKUP can greatly improve your workflow productivity and data-handling skills whether you are a business analyst, a student, or someone who needs to organise data effectively.
VLOOKUP stands for ‘Vertical Lookup,’ a function designed to search a specific value in the first column of the data range and return the value of the column that you specify in the same row. Usually, this function is used to cross-reference information between different tables and datasets. For instance, you can use VLOOKUP to find the price of a product based on its ID number.
Understanding VLOOKUP can save you countless hours of work. You can watch this video on VLOOKUP or continue reading the article below:
Here’s what we will cover:
- Syntax of VLOOKUP
- How to use VLOOKUP in Google Sheets?
- Approximate vs exact match
- Tips to optimise VLOOKUP
Syntax of VLOOKUP
VLOOKUP has the following syntax: VLOOKUP (search_key, range, index, is_sorted)
Now, let us understand each variable in the formula:
- search_key: This is the value that you want to search.
- range: Range sets the upper and lower limit of cells that VLOOKUP will search.
- index: The Index is the column number from which the function will retrieve the value.
- is_sorted: This variable is a bit difficult to understand. Also, it is optional, that is, you can choose to avoid it altogether. The default is TRUE providing you with an approximate value. While FALSE will return the exact match.
Also read: How to remove duplicates in Google Sheets?
How to use Vlookup in Google Sheets?
Now that we have understood the variables, let us understand how to use VLOOKUP in Google Sheets. Remember, to use VLOOKUP, to ensure that your data is organised and structured correctly.
Here’s an example:
| Product ID | Product Name | Price |
|---|---|---|
| 201 | Laptop | $1000 |
| 202 | Smartphone | $700 |
| 203 | Tablet | $400 |
For example, if you want to find the price of the product with ID no. 203, here’s what you will enter:
- search_key: 203
- range: A2:C4 (where A2:A4 contains Product IDs, B2:B4 contains Product Names, and C2:C4 contains Price)
- index: 3 (Price is in the third column of the range)
Here’s the complete formula: =VLOOKUP(102, A2:C4, 3, FALSE)
Press Enter and you will get the result: $400
You can also use the cell reference in VLOOKUP instead of hardcoding the search. For example, if the product ID is in the cell F1, the formula becomes: =VLOOKUP(F1, A2:C4, 3, FALSE). In the above table, the Product ID, 203, is in the cell A4. So, we’ll use A4 instead of 203. Take a look at the screenshot below:
Also read: How to add bullet points in Google Sheets?
Approximate vs exact match
Let us understand the difference between the approximate and exact match in VLOOKUP. As we have explained earlier, FALSE is used to return an exact match, while TRUE will give you an approximate value.
Approximate match
Consider the following formula: =VLOOKUP(201.5, A2:C4, 3, TRUE)
The answer will be the closest match, which $1000 for the Product ID 201. Take a look at the screenshot below:
Exact match
If you use the above formula and place FALSE instead of TRUE, you will see an error as there’s no exact match for 201.5
In other words, if you want to find the approximate value between numbers, use TRUE in the formula; otherwise, use FALSE.
Also read: How to indent in Google Sheets?
Tips to optimise VLOOKUP
Here are some tips to optimise VLOOKUP function in Google Sheets:
- Using VLOOKUP across sheets: You can use VLOOKUP to search data across different sheets. Here’s the formula: =VLOOKUP(A1, NewSheet!A2:C4, 3, FALSE). In this formula, NewSheet is the name of the Sheet you want to check. Remember to include the exclamation mark after the sheet’s name before entering the cell range.
- Advanced lookup: You can combine VLOOKUP with other functions like IF, MATCH, and RIGHT for more advanced search.
- Keep data sorted: Sorting your data in ascending order will allow you to search for the approximate value. If the data isn’t sorted, you cannot use it to search for the approximate value.
- Use named range: You can use named range instead of cell references for clarity.
- Limit range: You can use a small limit range for increased performance.
In conclusion, by understanding the VLOOKUP function, you can easily streamline your data analysis tasks and increase your productivity.
Also read: How to rename columns in Google Sheets?
