If you work with a large dataset in Google Sheets and need to find and bring together specific information quickly, the VLOOKUP function is the number one solution. Let's say, you have a list of products and their prices in one table and a separate table with customer orders. You can also use VLOOKUP to match these tables and save time manually cross-referencing and ensuring accurate pricing in your records.
With a few tricks, you will confidently handle data and won't make mistakes in your formulas. In this article, we'll walk you through these tips and tricks so that you can start using this function without any challenges.
VLOOKUP in Google Sheets is a function that helps you find and retrieve information from a table. It's similar to a search function for your spreadsheet. You give it a value to look for, tell it where to search, and then it retrieves the corresponding data. It's convenient if you need to quickly get specific data without manually scanning through a large dataset.
🎥 Looking for a more visual explanation? Watch our detailed video on using VLOOKUP in Google Sheets. This video complements the article and provides step-by-step guidance to enhance your understanding.
Using VLOOKUP in Google Sheets has several advantages that help handle data. Let's look at 5 of them:
This is basically a search tool that helps you find information way faster by using a search key to locate specific data rather than looking through everything manually.
You can bring all your data from different tables into one place. It is crucial that the data you want to match is in the same row across different tables for accurate results.
The formula helps reduce mistakes by automatically finding the right data. Using the FALSE option in your VLOOKUP formula ensures that only exact matches are considered, reducing the likelihood of errors.
It makes sure your data is correct by checking it against lists or tables you already set up. This way, you avoid making errors when entering information.
Having all the details at your fingertips allows you to make quicker and better decisions.
This is the standard VLOOKUP formula:
=VLOOKUP(search_key, range, index, [is_sorted])
Now that you have a better grasp of the function, let's see the examples of using VLOOKUP. Here are various ways you can apply VLOOKUP formulas in Google Sheets:
Let's create 2 simple tables in 1 spreadsheet to demonstrate a Google Sheet VLOOKUP: 1st for "Products" and 2nd for "Sales." Our goal is to find the price of a product in the sales table based on the product name using the VLOOKUP formula.
Here is how to do that:
1. Create the "Price" column in the "Sales" table, and use the following formula in cell F2:
=VLOOKUP(A3, A:B, 2, FALSE)
This formula looks up the product name (e.g., "Apple") in the "Products" table, fetches the corresponding price, and displays it in the "Sales" table.
2. After dragging the formula down for other products, the "Sales" table would look like this:
You can use VLOOKUP for a partial match to locate a string value containing specific criteria. Let's find someone's email address based on the initial characters of their name:
The resulting formula is:
=VLOOKUP(E3&"*", A3:B6, 2, FALSE)
VLOOKUP delivers the value in E3, which is elizabeth@example.com.
You can use VLOOKUP in Google Sheets with an exact match (FALSE) to find the result only if there is an identical value in the search column. In this example, we'll identify a company name based on someone's email address:
The resulting formula is:
=VLOOKUP(F2, A3:C6, 3, FALSE)
VLOOKUP delivers the value in F3, which is Rockwell Automation.
You can compare 2 columns and identify common values. Let's discover identical email addresses in both columns:
The resulting formula is:
=VLOOKUP(A3, B$3:B$8, 1, FALSE)
Email addresses identical in both lists will appear in the second column of D. An “#N/A” error will appear on each row where the email addresses are not available in the Right list.
By default, VLOOKUP doesn't support multiple criteria searches. However, you can use VLOOKUP with a helper column to combine multiple criteria. In this example, we have a specified column that'll locate someone's email address based on their first and last name:
1. Create a Helper column by combining the values of the First name and Last name columns:
1.1. Type = in cell A3, the first cell in the column.
1.2. Enter B3 (Adams), the initial value to use as criteria.
1.3. Add & to combine 2 values.
1.4. Enter C3 (Presley), the second value to use as criteria.
1.5. Drag the formula down to merge values for each row.
1.6. Enter =VLOOKUP in cell G4, where you want the email address:
2. Input the LOOKUP values G2&G3, containing the First name (Adams) and Last name (Presley) you're searching for.
3. The ampersand (&) combines the 2 lookup values.
4. Specify the Search range as A3:D5, the data range containing the Helper, First name, Last name, and Email values.
5. Ensure the helper column is the 1st column of the Search range.
6. Choose Column number 4, as the Email column is the 4th column of the Search range.
7. Use FALSE to look for an exact match.
The resulting formula is:
=VLOOKUP(G2&G3,A3:D5, 4, FALSE)
VLOOKUP puts the value in G4, which is adams@example.com.
💡 For detailed instructions on using VLOOKUP or XLOOKUP for specific value lookups or one-to-one dataset matching, check out our comprehensive guide. It includes practical use cases and templates to help you get started.
Let's say you have 2 sheets: “Employees” and “Sales.” Here’s how to fetch the Hourly Rates for Employee IDs E010 and E014 from the “Employees” sheet and display them in cells B3 and B4 on your “Sales” sheet.
To use VLOOKUP across different sheets in separate spreadsheet files, include the IMPORTRANGE function. Here's a quick guide using the previous example about “Employees” and “Sales” tables:
The final formula looks like this:
=VLOOKUP(A3,IMPORTRANGE("URL","Employees!$A$3:$C$8"),3)
If a #REF! error appears, make sure to click "Allow access" to connect the source and the target sheets.
Now, you'll see the hourly rates corresponding to Employee ID E010 in cell B3. Drag down the formula to fill in the rest of the cells in the column.
💡 Essential for bringing in data from multiple external sources, the IMPORTRANGE function significantly improves the capabilities of data analysis. Check out our complete guide on using IMPORTRANGE, and get a free template to make the most of this powerful tool.
While Google Sheet VLOOKUP may appear to be a simple formula, even the smallest errors can bring inaccurate results. Here are some common issues and troubleshooting tips:
Issue #1: The data types of the search_key in both tables are different.
Solution: Ensure that the data type of the search_key in both tables is consistent.
For example, if one lookup table uses employee IDs as numbers (e.g., 123) and the other table stores the same IDs as text (e.g., "123"), this discrepancy in data types will cause mismatches during the lookup process. To fix this, ensure that the data types for the search_key are uniform in both tables.
Issue #2: Using an approximate match (TRUE) without sorting the search_key column.
Solution: If using an exact match, set the [is_sorted] argument to FALSE. If using an approximate match, make sure the search_key column is sorted in ascending order.
Issue #3: Hidden or non-printable characters in cell values.
Solution: If you have characters like line breaks, carriage returns, tabs, and other special characters in your cell values that can’t be displayed in a visible form use the CLEAN function to remove non-printable characters: =CLEAN(cell_reference).
Issue #4: Extra spaces in cell values causing mismatches.
Solution: Use the TRIM function to remove extra spaces: =TRIM(cell_reference).
Issue #5: VLOOKUP is case-sensitive by default.
Solution: Use the LOWER or UPPER functions to standardize case: =VLOOKUP(LOWER(search_key), range, index, FALSE).
Issue #6: The search_key is not within the specified range.
Solution: Double-check that the search_key is within the specified range and that the range is correct.
Issue #7: The search_key does not exist in the range.
Solution: Confirm that the search_key exists in the specified range. Check for spelling errors or variations.
Issue #8: There are multiple occurrences of the search_key.
Solution: VLOOKUP returns the first match it finds. If you want to handle multiple matches, consider other functions like INDEX and MATCH.
Issue #9: Manually dragging the formula without updating cell references.
Solution: When dragging the formula to other cells, ensure that the cell references are updated correctly. If the formula doesn’t update automatically, modify it manually by ensuring the references reflect the correct cells for accurate calculations.
Issue #10: Referencing data in different sheets or files incorrectly.
Solution: Ensure that you reference data on the same sheet name in other sheets or files accurately. Use single quotes for sheet names: 'Sheet Name'!A:B.
Google Sheets is equipped with a variety of powerful formulas that streamline your data analysis efforts-
While VLOOKUP offers valuable solutions for finding data in spreadsheets, it also requires a lot of manual work, potentially leading to errors, especially when managing big datasets.
With OWOX Reports Extension for Google Sheets, you don't have to bother with manual formulas — it does the work for you, making your tasks simpler. You can import data straight from Google Sheets without dealing with CSV files. This time-saving feature, combined with a user-friendly query editor, makes report creation even more convenient.
Additionally, in the settings of the OWOX Reports Extension for Google Sheets, you can schedule automatic updates for your reports.
Yes, VLOOKUP can search for values both vertically and horizontally in Google Sheets. The orientation depends on how the data is arranged in the specified range.
You set the range for a VLOOKUP search by indicating the sheet name, if applicable, followed by an exclamation mark (!), and then providing the cell range within which you want to search.
VLOOKUP in Google Sheets is not case-sensitive. To perform a case-sensitive lookup, you can use a combination of functions like EXACT or helper columns to convert text to a consistent case.
To reference data from another tab using VLOOKUP, include the sheet name followed by an exclamation mark (!) before setting the cell range in the formula.
VLOOKUP in Google Sheets searches from left to right. If you need to search from right to left, you can use the INDEX and MATCH functions combined.
Yes, VLOOKUP can search data from another sheet in Google Sheets. You need to select the range using the sheet name, followed by an exclamation mark (!), and then the cell range.
The "column index number" in a VLOOKUP formula refers to the column from which you want to retrieve data. It shows the relative position of the column within the specified range.
VLOOKUP in Google Sheets is used to search for a specified value in a column, and when found, retrieve information from the same row in a different column. It's commonly used for data analysis, lookup operations, and organizing information.