Mastering VLOOKUP in Google Sheets

Stop scrolling and start searching. Learn how to find data instantly and merge tables using the VLOOKUP function.

October 27, 2025 • 5 min read

If there is one function that separates beginners from intermediate spreadsheet users, it is VLOOKUP. Whether you are managing inventory, analyzing sales data, or merging mailing lists, VLOOKUP (Vertical Lookup) is your best friend. This guide will walk you through how to master it.

1. The Syntax Breakdown

The function might look intimidating at first, but it follows a simple logic. The syntax is:

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

  • search_key: What are you looking for? (e.g., a Product ID).
  • range: Where should we look? (The table containing the data).
  • index: Which column has the answer? (1 for the first column, 2 for the second, etc.).
  • is_sorted: Usually FALSE (or 0) for an exact match.

2. A Real-World Example

Imagine you have a list of Order IDs in Column A and you want to pull the Customer Name from a separate "Customers" table (Columns D to E). The Customer Name is in the second column of that table.

Your formula would look like this:

=VLOOKUP(A2, D:E, 2, FALSE)

This tells Sheets: "Look for the value in A2 inside the range D:E. When you find it, give me the value from the 2nd column. Only accept an exact match."

3. Always Use Absolute References

One of the most common mistakes is forgetting to lock the range. If you copy your formula down, the range D2:E100 will shift to D3:E101, potentially missing data.

Fix: Use dollar signs to lock the range: $D$2:$E$100. Or, simply reference the entire columns like D:E.

4. Handling "Not Found" Errors

If VLOOKUP cannot find your search key, it returns the dreaded #N/A error. This can make your dashboard look broken.

Wrap your function in IFERROR to display a friendly message instead:

=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), "Not Found")

5. The "Left-to-Right" Rule

VLOOKUP has a strict rule: it can only look to the right. Your search_key must be located in the first column of the range you select.

If your search key is in Column E and the data you want is in Column D, VLOOKUP won't work directly. In that case, you might need to rearrange your columns or use the more flexible INDEX and MATCH combination.

6. Watch Out for Data Types

A common frustration occurs when VLOOKUP returns #N/A even though the value clearly exists. This often happens because of data type mismatches.

For example, the number 1001 is not the same as the text string "1001". Ensure both your search key column and your source column are formatted the same way.

7. Using Wildcards

Did you know VLOOKUP supports wildcards? This is useful for partial matches.

  • ? (Question mark): Matches any single character.
  • * (Asterisk): Matches any sequence of characters.

Example: =VLOOKUP("Sm*", A:B, 1, FALSE) will find the first name starting with "Sm" (like Smith or Small).

Mastering VLOOKUP opens the door to powerful data analysis. Once you are comfortable with these basics, you'll find yourself navigating spreadsheets with much greater speed and confidence.