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.

Everything about VLOOKUP in Google Sheets

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.

What is VLOOKUP in Google Sheets?

What is VLOOKUP in Google Sheets?

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.

The Benefits of Using VLOOKUP in Google Sheets

Using VLOOKUP in Google Sheets has several advantages that help handle data. Let's look at 5 of them:

1. Find data easier

This is basically a search tool, which helps you find information way faster than looking through everything by hand.

2. Puts everything together

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

3. Less mistakes, more accuracy

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

4. Keeps data in check

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.

5. Decide faster

Having all the details at your fingertips allows you to make quicker and better decisions.

The Syntax for VLOOKUP in Google Sheets

This is the standard VLOOKUP formula:

=VLOOKUP(search_key, range, index, [is_sorted])

  • 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.

How to use VLOOKUP in Google Sheets (with formula examples)

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:

Simple VLOOKUP

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:

=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:

VLOOKUP partial match in Google Sheets

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(E3&"*", A3:B6, 2, FALSE)

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

VLOOKUP with an exact match in Google Sheets

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(F2, A3:C6, 3, FALSE)

VLOOKUP delivers the value in F3, which is Rockwell Automation.

Comparing 2 columns with VLOOKUP in Google Sheets

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:

=VLOOKUP(A3, B$3:B$8, 1, FALSE)

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.

VLOOKUP with multiple criteria in Google Sheets

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(G2&G3,A3:D5, 4, FALSE)

VLOOKUP puts the value in G4, which is adams@example.com.

VLOOKUP on another Sheet within the same spreadsheet file

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.

  1. Enter the formula: =VLOOKUP(A3,
    Here, A3 is the cell with the value you want to look up.
  2. 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,
  3. 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.

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

VLOOKUP from another sheet in a separate spreadsheet file

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”).

  1. 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.
  2. 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:

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

Grab Your Comprehensive VLOOKUP Template Now!

Every function and example from this article is ready for you to use and apply instantly. Just download, click, and start practicing.

Common Google Sheets VLOOKUP Issues And Tips to Solve Them

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.

Boost Your Data Analysis With Automated Tools

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, making data management and analysis more accurate and faster.

OWOX BI Helps You Analyse Bigquery Data in Google Sheets

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.

table

Make Your Corporate BigQuery Data Smarter in Sheets

Transform Google Sheets into a dynamic data powerhouse for BigQuery. Visualize your data for wise, efficient, and automated reporting

Transform Your Reporting

FAQ

Expand all Close all
  • Can VLOOKUP search for values vertically and horizontally in Google Sheets?

    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.
  • How do you specify the range for a VLOOKUP search in Google Sheets?

    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.
  • How do you handle case-sensitive lookups using VLOOKUP in Google Sheets?

    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.
  • How do you reference data from another tab in Google Sheets using VLOOKUP?

    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.
  • How do you modify VLOOKUP to search from right to left in Google Sheets?

    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.
  • Is it possible to use VLOOKUP to search data from another sheet in Google Sheets?

    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.
  • What does the "column index number" mean in a VLOOKUP formula?

    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.
  • What is VLOOKUP in Google Sheets used for?

    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.