How To

How to Use VLOOKUP in Google Sheets: A Step-by-Step Guide

Learn how to use VLOOKUP in Google Sheets with this step-by-step guide, including useful tips, examples, and error-handling techniques.

VLOOKUP is one of the most widely used functions in Google Sheets, especially for handling large datasets. It allows you to search for specific data in one column and retrieve corresponding information from another column.
Whether you're managing financial reports, inventory data, or sales records, VLOOKUP simplifies the process of data lookup.
In this blog post, we will cover everything you need to know about using VLOOKUP in Google Sheets, including a step-by-step guide, useful tips, and key insights to get the most out of this powerful function.

What is VLOOKUP?

VLOOKUP (Vertical Lookup) is a function in Google Sheets that searches for a value in the first column of a range (called the "lookup table") and returns a value in the same row from another column of the range.
The formula looks like this:
  • search_key: The value you want to look for.
  • range: The range of cells that includes the data you are searching through.
  • index: The column number (starting from 1) from which the matching value will be returned.
  • is_sorted: A TRUE or FALSE value that specifies whether the first column of the range is sorted. Typically, FALSE is used to find an exact match.

Steps to Use VLOOKUP in Google Sheets

Step 1: Organize Your Data

Before using VLOOKUP, ensure that your data is organized. The column you are searching through (the lookup column) must be the first column in your range.
Example:
Product Code
Product Name
Price
101
Apple
$1
102
Banana
$0.5
103
Orange
$0.8
Here, we can use VLOOKUP to search by the Product Code and return the Product Name or Price.

Step 2: Select the Cell Where You Want to Enter the Formula

Click on the cell where you want the VLOOKUP result to appear.

Step 3: Enter the VLOOKUP Formula

Use the following format for the VLOOKUP formula:
  • 101: This is the search key (Product Code).
  • A2: This is the range of data to search through.
  • 2: This is the index number (column number) of the value you want to return. In this case, "Product Name" is in the second column.
  • FALSE: This ensures that the formula looks for an exact match of the search key.
Press Enter, and the function will return the value "Apple."

Step 4: Adjust for Different Columns

You can change the column index (third argument) to retrieve data from a different column. For example, if you want to return the Price instead of the Product Name:
In this case, it would return "$1" because the Price is in the third column of the range.

Step 5: Handle Errors with IFERROR

Sometimes, VLOOKUP may not find a match and return an error. To avoid this, you can wrap the VLOOKUP function in an IFERROR function:
If VLOOKUP can't find the value, it will display "Not Found" instead of an error.

Key Tips for Using VLOOKUP

  1. Use Exact Match (FALSE): Always use FALSE as the final argument for an exact match. Using TRUE would assume that the first column is sorted, and Google Sheets will return an approximate match, which can lead to incorrect results.
  1. Column Indexing Starts from 1: Remember that the index in VLOOKUP starts from 1. So, if you're retrieving data from the second column, use 2 as the column index.
  1. Data Formatting Matters: Ensure that the data in your lookup column and the search key are formatted the same. For example, if your search key is a number, the data in the lookup column must also be a number, not text.
  1. Use Named Ranges: Instead of using a fixed range like A2:C4, you can use named ranges to make your formulas easier to read and manage. To name a range, select the range, click Data > Named ranges, and give it a meaningful name like "ProductData." Your formula will now look like this:
  1. Make the Range Dynamic: If your dataset grows regularly, you may want to make the range dynamic by using whole columns (e.g., A:C instead of A2:C4). However, this can slow down your Google Sheet if you have a lot of data.
  1. Handle Missing Data with IFERROR: As mentioned earlier, it's always good to use IFERROR to handle missing or incorrect data. This ensures that your sheet looks clean and professional, even when the data isn't available.
  1. Combine VLOOKUP with Other Functions: You can combine VLOOKUP with other functions like ARRAYFORMULA, IMPORTRANGE, or SUMIF for more advanced data analysis. For instance, you can use IMPORTRANGE to look up data from another sheet.

Common VLOOKUP Errors and How to Fix Them

  1. #N/A Error: This error occurs when the search key isn't found in the lookup column. Double-check that the search key exists, and consider using IFERROR to display a custom message.
  1. #REF! Error: This happens when the column index is larger than the number of columns in the range. Ensure the index number corresponds to a valid column.
  1. #VALUE! Error: This error may appear if the range isn't correctly defined or if the formula includes invalid data types (e.g., text instead of numbers).

When to Use VLOOKUP vs. Other Functions

  • VLOOKUP vs. INDEX-MATCH: While VLOOKUP is easier for simple lookups, INDEX-MATCH is more versatile. If you need to search for a value in any column (not just the first), consider using the INDEX-MATCH combination.
  • VLOOKUP vs. FILTER: If you need to return multiple columns at once, consider using FILTER instead. VLOOKUP only retrieves data from a single column at a time.

Conclusion

VLOOKUP in Google Sheets is an invaluable tool for quickly finding and returning data from large datasets. By following the steps above and keeping the tips in mind, you can effectively use VLOOKUP for a variety of purposes, from generating reports to managing inventories. Remember to always use exact match mode, ensure your data is well-formatted, and handle potential errors with IFERROR to make your spreadsheets more reliable and user-friendly.