This guide covers everything you need to know about the Google Sheets VLOOKUP function, including its definition, syntax, use cases, and how to use it.
What is the VLOOKUP Function? How Does It Work?
The VLOOKUP function in Google Sheets returns a value from a column you specify in the range, searching for that value in the leftmost column of the range.
The VLOOKUP function, standing for Vertical Lookup, is a powerful feature in Google Sheets that allows users to extract and manipulate data from large databases. Its primary purpose is to retrieve specific information based on a user-defined condition from a data set.
The function works by searching for a particular value in a selected range’s first (leftmost) column. Once it finds a match, it returns a corresponding value from the same row but in a column specified by the user.
Consider a scenario where you have a large spreadsheet filled with an extensive list of products, their corresponding codes, prices, and other details. If you wish to quickly find the price of a product using its unique code, manual scanning might prove time-consuming. This is where the VLOOKUP function becomes handy. You can instruct Google Sheets to look up the product code within the leftmost column and return the price from the column you’ve specified.
It’s crucial to understand how the VLOOKUP function operates to fully leverage its potential. It starts searching from top to bottom of the specified range, and once it finds an exact match or the closest match (in numerical cases), it stops searching further. Thus, if there are duplicate values in the leftmost column, VLOOKUP will only consider the first instance.
Moreover, it’s important to note that VLOOKUP will only search for values in a leftward to rightward manner. This means the function can pull data from columns to the right of the lookup column but not to the left. This has implications for how you organize your data for optimal use of the VLOOKUP function.
VLOOKUP Syntax
The syntax and arguments for the function are as follows:
VLOOKUP(search_key, range, index, [is_sorted])
- search_key: This is the value that the VLOOKUP function uses to search. This value can be a number, text, a logical value, or a name or reference that refers to a value.
- range: This is the range of cells in which the VLOOKUP function searches for the search_key. The first column in the range is searched for the key specified in search_key.
- index: This is the column index of the value to be returned, where the first column in range is numbered 1. If index is not between 1 and the number of columns in range, VLOOKUP returns an error.
- [is_sorted]: This is an optional argument. If this argument is TRUE or omitted, the first column in the range is sorted, and the function returns the closest match. If this argument is FALSE, the first column in the range is not sorted, and the function returns an exact match. If an exact match is not found, the function returns an error.
Usage notes related to syntax and arguments include:
- The VLOOKUP function is not case-sensitive. So, for example, a search_key of “APPLE” and “apple” will return the same result.
- If the search_key is text, you can use wildcard characters — the question mark (?) and asterisk (*) — in the search_key. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
- If the range contains merged cells, the results may be incorrect.
- The VLOOKUP function can return a field from a different row by using a relative position. For example, you can retrieve the fifth field from the row that contains a certain value in the first field.
- VLOOKUP will only look for a closest match to the search_key in the leftmost column of the range. If the search_key does not exist in the leftmost column of the range, VLOOKUP will return an error.
- If the [is_sorted] argument is set to TRUE or is omitted, and the first column of the range is not in sorted order, the return value might be something you do not expect.
Examples of How to Use the VLOOKUP Function
Here are some practical examples of how to use the VLOOKUP function in Google Sheets.
Example #1: Looking Up Prices in a Product List
Let’s say you have a product list in columns A and B, with product names in column A and their corresponding prices in column B. You want to find the price of a specific product, say “Product X”. You can use the VLOOKUP function to do this.
In any cell, type:
=VLOOKUP(“Product X”, A:B, 2, FALSE)
This formula tells Google Sheets to look for “Product X” in column A, then return the corresponding value from the same row in column B.
Example #2: Matching Student Names with Grades
Suppose you have a list of students’ names in column A and their grades in column B. You want to find the grade of a particular student, say “John Doe”. You can use the VLOOKUP function as follows:
=VLOOKUP(“John Doe”, A:B, 2, FALSE)
This formula will search for “John Doe” in column A and return the corresponding grade from column B.
Example #3: Finding Employee Details
Assume you have an employee database with Employee ID in column A and their details in the next columns. You want to find the details of a specific employee by their ID. Here’s how you can use the VLOOKUP function:
=VLOOKUP(12345, A:E, 3, FALSE)
This formula will search for the Employee ID 12345 in column A and return the corresponding detail from column C (since 3 is the column index number).
Example #4: Locating Book Information
Let’s say you have a list of books with ISBN numbers in column A and book titles in column B. You want to find the title of a book using its ISBN number. You can use the VLOOKUP function like this:
=VLOOKUP(9781234567890, A:B, 2, FALSE)
This formula will search for the ISBN number in column A and return the corresponding book title from column B.
Remember, the VLOOKUP function in Google Sheets is case-insensitive and it always searches in the first column of the range for a key and returns the value of a specified cell in the row found.
VLOOKUP: Common Mistakes & Problems
When using the VLOOKUP function in Google Sheets, there are several common mistakes and problems that you may encounter. Understanding these issues can help you troubleshoot any errors or unexpected results. Here are some of the most frequent concerns:
- Incorrect Reference: One of the most common mistakes when using VLOOKUP is referencing the wrong range. The function will only search in the first column of the range you specify. If your lookup value is not in the first column of the range, VLOOKUP will not return the correct result.
- Non-Unique Lookup Values: VLOOKUP will only return the first match it finds. If there are multiple rows with the same lookup value in the first column of your range, VLOOKUP will not return the other matches. If you need to find all matches, you may need to use a different function or approach.
- Not Using Absolute Cell References: If you’re copying your VLOOKUP formula to other cells, you should use absolute cell references for your range. If you use relative references, the range will shift when you copy the formula, and VLOOKUP may not return the correct result.
- Lookup Value Does Not Exist: If the lookup value does not exist in the first column of your range, VLOOKUP will return an #N/A error. Make sure your lookup value exists in the range.
- Approximate Match vs. Exact Match: By default, VLOOKUP performs an approximate match, which means it will find the closest match to your lookup value. If you need an exact match, you need to set the last argument of the function to FALSE or 0.
- Data Type Mismatch: VLOOKUP may not work correctly if your lookup value is a different data type than the values in the first column of your range. For example, if your lookup value is a number and the first column of your range contains text, VLOOKUP will not find a match.
- Range Lookup Value is Less Than the Smallest Value in the First Column: If the range lookup is set to TRUE and the lookup value is less than the smallest value in the first column of the range, VLOOKUP will return an #N/A error.
Why Is VLOOKUP Not Working? Troubleshooting Common Errors
If you’ve followed all the steps but your VLOOKUP function still isn’t working, you could be encountering one of several common errors. Let’s walk through these, their causes, and how to fix them.
#N/A Error
Cause: The #N/A error typically appears when the VLOOKUP function can’t find the lookup value. This could be because the lookup value doesn’t exist in the first column of the table array or because there is a discrepancy between data types.
Solution: Double-check your data. Ensure that the lookup value exists in the first column of the table array and that there are no discrepancies between data types. For instance, if the lookup value is a number, ensure that it’s not stored as text in the table array.
#REF! Error
Cause: The #REF! error usually occurs when the col_index_num argument in the VLOOKUP function is less than 1 or greater than the number of columns in the table array.
Solution: Check the col_index_num argument. Ensure it corresponds to the correct column in your table array and that it’s not less than 1 or greater than the number of columns in the table array.
#NAME? Error
Cause: The #NAME? error signifies that Google Sheets doesn’t recognize the text in the formula. This error often happens when the formula is misspelled, the named range is spelled incorrectly, or the named range doesn’t exist.
Solution: Check your spelling. Ensure the VLOOKUP function and any named ranges used in the formula are spelled correctly. If you’re using a named range, confirm that it exists and is correctly defined.
#VALUE! Error
Cause: The #VALUE! error generally appears when the range argument in the VLOOKUP function isn’t a valid reference. This could be because the range includes non-numeric characters, or because the range is not properly defined.
Solution: Review your range argument. Make sure it’s a valid reference and does not include any non-numeric characters. If the range is defined by cell references, ensure that these are accurate and correctly formatted.
By identifying the type of error message you’re seeing, you can quickly pinpoint what might be going wrong with your VLOOKUP function, and apply the appropriate solution to resolve the issue.
Using VLOOKUP With Other Google Sheets Functions
Combining VLOOKUP with other Google Sheets functions can be a powerful way to analyze and manipulate data. Here are a few examples of how you can use VLOOKUP with other Google Sheets functions.
With IFERROR
The IFERROR function is used to return a custom result when a formula generates an error and a standard result when no error is detected. When combined with VLOOKUP, you can use IFERROR to return a specific value or message when VLOOKUP can’t find the lookup value.
Example:
Let’s say you have a list of employees’ IDs in one sheet and their corresponding names in another. If you were to use VLOOKUP to find the name of an employee based on their ID, there might be instances where the ID doesn’t exist in the second sheet. In such cases, you can use IFERROR to display a message like “Employee Not Found”.
=IFERROR(VLOOKUP(A2,Sheet2!$A$1:$B$100,2,FALSE), “Employee Not Found”)
With MATCH
The MATCH function in Google Sheets is used to return the relative position of an item in a range that matches a specified value. When you combine MATCH with VLOOKUP, you can dynamically update the column index number in your VLOOKUP function.
Example:
Let’s say you have a dataset where the columns may change position. Instead of manually updating the column number in your VLOOKUP function, you can use MATCH to find the column number for you.
=VLOOKUP(A2,Sheet2!$A$1:$Z$100,MATCH(“Employee Name”,Sheet2!$A$1:$Z$1,0),FALSE)
With ARRAYFORMULA
The ARRAYFORMULA function allows you to apply a formula to an entire column or row instead of applying the formula to each cell individually. When combined with VLOOKUP, you can look up values based on an entire column of lookup values.
Example:
If you have a column of product IDs in Sheet1 and want to find the corresponding product names from Sheet2, you can use ARRAYFORMULA with VLOOKUP to fill an entire column with the respective product names.
=ARRAYFORMULA(VLOOKUP(A2:A,Sheet2!$A$2:$B$100,2,FALSE))
For more details on the VLOOKUP function, check out the official documentation at the Google Docs Editors Help Center.
