Everything about VLOOKUP in Google Sheets

If you work with a large dataset and need to find and bring together specific information quickly, VLOOKUP 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 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 finds the corresponding information. It's convenient if you need to quickly get specific data without manually scanning through a large dataset.

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, which helps you find information way faster than looking through everything by hand.

You can bring all your data from different tables into one place.

The formula helps reduce mistakes by automatically finding the right data.

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:

• search_key: What you're searching for, like a name or a number.
• range: Where you're searching, usually the columns with your data.
• index: The column with the information you need, like prices or names.
• is_sorted: This part is optional. If you put FALSE, it looks for an exact match (which is often a good idea). If you don't put anything, it assumes you want an approximate match.

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 in Google Sheets:

Let's create 2 simple tables in 1 spreadsheet: 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:

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:

1. Type =VLOOKUP in the cell, where you want the email address to appear. Input the Lookup value E3&"*" which holds the prefix of the Name (“eli” for elizabeth@example.com) you aim to find.
2. The ampersand (&) merges the LOOKUP value with the wildcard ("*"). Placing the wildcard at the end creates a "begins with" match. This prompts VLOOKUP to match the initial entry in column A (Name) that starts with the prefix "eli".
3. Specify the Search range as A3:B6, the data range containing Name and Email values. Choose Column number 2, as the Email column is the 2nd column of the Search range. Use FALSE to seek an exact match.

The resulting formula is:

VLOOKUP delivers the value in E3, which is elizabeth@example.com.

You can use VLOOKUP 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:

1. Type =VLOOKUP in cell F3, where you want the Company name to appear.
2. Input the Lookup value F2, which holds the email (james@ra.com) you wish to find.
3. Specify the Search range as A3:C6, the data range containing all the email and company values.
4. Choose Column number 3, as the Company column is the 3rd column of the Search range.
5. Use FALSE to seek an exact match.

The resulting formula is:

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:

1. Type =VLOOKUP in cell D3, where you want to initiate the list of email addresses common to both columns.
2. Input the LOOKUP value A3, which contains the initial email address of the Left list (james@example.com).
3. Specify the Search range as B$3:B$8, the data range containing all the email addresses of the Right list. Include the dollar sign ($) to fix the rows, ensuring that when you drag down the formula, VLOOKUP consistently refers to the same rows.
4. Opt for Column number 1 since the Search range consists of only one column (Right list), and the Lookup value is equal to the Return value in this case.
5. Use FALSE to seek an exact match.
6. Drag the formula down through as many cells as there are values in the Left list.

The resulting formula is:

Email addresses identical in both lists will appear in column 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'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 puts the value in G4, which is adams@example.com.

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.

1. Click on the first cell of your target column, which is B3 in the “Sales” sheet. 
2. Enter the formula: =VLOOKUP(A3,
   Here, A3 is the cell with the value you want to look up.
   
3. Define the search range in the other sheet. Navigate to the “Employees” tab. Select the cell range you want the VLOOKUP function to search (A3:C8). Lock the cell range by pressing F4 (or Ctrl+F4) to prevent changes when copying the formula. The VLOOKUP formula should now look like this: =VLOOKUP(A3, Employees!$A$3:$C$8,
4. Input the VLOOKUP index. After $C$8, add a comma followed by the column index with the values you want to retrieve. Input “3” since we're fetching the Hourly Rates (the 3rd column in the A3:C8 range). Add the closing parenthesis.

5. Drag the formula down to fill the rest of the cells in column B.

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:

1. Click on the cell where you want the result (e.g., B3 in “Sales”).
2. Begin with =VLOOKUP and choose the lookup value (e.g., A3), followed by a comma.
3. Introduce IMPORTRANGE in the formula with an opening parenthesis.
4. Go to the source spreadsheet (e.g., “Employees”), copy its URL, return to the target spreadsheet (e.g., “Sales”), and paste the URL into IMPORTRANGE within double quotes. Add a comma and specify the source sheet's name (e.g., “Employees”).

5. Add an exclamation mark (!), then input the cell ranges from the source sheet (e.g., $A$3:$C$8), and enclose the entire parameter in double quotes, followed by a comma and closing parenthesis.
6. Add a comma and the column index for the values you're retrieving (e.g., “3”), followed by a closing parenthesis.

The final formula looks like this:

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 the rest of the cells in the column.

While 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 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 in other sheets or files accurately. Use single quotes for sheet names: 'Sheet Name'!A:B.

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.

If manual processes are a headache for you, find a tool that will help you automate data retrieval and get rid of the limitations of the standard VLOOKUP formula with IF statment, making data management and analysis more accurate and faster.

Google Sheets is equipped with a variety of powerful formulas that streamline your data analysis efforts:

• XLOOKUP: A step up from VLOOKUP, this function brings improved data search and retrieval capabilities to your spreadsheets, enhancing flexibility.
• ARRAY: Perfect for performing calculations across numerous data points at once, this formula delivers results in an array format.
• IMPORT Functions: Crucial for pulling data into your spreadsheet from external sources like web pages, other Google Sheets, or RSS feeds.
• Pivot Table: Facilitates efficient data summary and analysis, helping you quickly spot patterns and trends through automated organization.
• QUERY: Employs a SQL-esque language for intricate data operations within your sheet, including advanced filtering, sorting, and compiling.
• CONCATENATE: Links together several text segments into a single string, simplifying the combination of text from various cells.
• UNIQUE: Filters out duplicate entries from a specified data range, leaving only unique values.

With OWOX BI, 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 BI BigQuery Reports Extension, you can schedule automatic updates for your reports.