VLOOKUP in Google Sheets: Complete Guide

VLOOKUP is one of the most useful functions in Google Sheets. It searches for a value in the first column of a range and returns a corresponding value from another column. Whether you’re matching product IDs to prices or employee names to departments, VLOOKUP handles it.

This guide covers everything: syntax, step-by-step examples, common errors, and when to use alternatives like INDEX MATCH.

VLOOKUP Syntax

Watch the four arguments explained (1:21)

Here is the complete VLOOKUP syntax in Google Sheets:

Step-by-Step Example

Watch this build, step by step (2:55)

Let’s walk through a real example. You have a product inventory and want to look up prices by product ID.

Sample Data

Decide what you are looking for. In this case, you want to find the price for product ID SKU-103.

Click on an empty cell and enter the formula:

Here is what each part means:

• "SKU-103": the product ID to search for
• A2:D6: the data range (your lookup table)
• 3: return the value from the 3rd column (Price)
• FALSE: find an exact match

The formula returns $35.00, which is the price for the Pocket Watch.

Instead of hardcoding the search value, reference a cell:

Now you can type any product ID in cell F2, and the formula updates automatically.

Common VLOOKUP Errors

Watch all three errors get fixed (5:14)

#N/A Error

The #N/A error means VLOOKUP could not find your search key in the lookup range. Common causes:

• Typos in the search key or data
• Extra spaces: use TRIM() to clean data
• Mismatched types: searching for text “123” in a column of numbers

For a full walkthrough of every failure mode and its fix, see fixing VLOOKUP errors.

Fix it by wrapping VLOOKUP in IFERROR:

#REF! Error

This happens when your index number exceeds the number of columns in the range. If your range has 4 columns, the index must be between 1 and 4.

#VALUE! Error

The #VALUE! error usually means the index parameter is not a valid number, or the search key is in the wrong format. For example, passing a column name instead of a number triggers it:

That returns #VALUE! because "Price" is not a column number. Use 3 instead. Note that a quoted number like "3" does not error, because Google Sheets coerces it to a number first.

VLOOKUP with Approximate Match

Watch approximate match in action (6:52)

Setting the last parameter to TRUE enables approximate matching. VLOOKUP finds the largest value less than or equal to your search key.

This is useful for grade boundaries, tax brackets, or commission tiers:

This returns B because 85 falls between 80 and 89.

There is more on sorting rules and tier tables in VLOOKUP approximate match.

VLOOKUP vs INDEX MATCH

Watch the three lookups compared (8:02)

For straightforward right-lookups, VLOOKUP works well. For anything more complex, INDEX MATCH is the better choice. And if you need to match on more than one condition, there is a workaround in VLOOKUP with multiple criteria.

Best Practices

1. Always use exact match, Set the fourth parameter to FALSE unless you specifically need approximate matching.

2. Lock your range with absolute references, Use $A$2:$D$6 instead of A2:D6 when copying formulas across multiple rows.

3. Wrap in IFERROR, Handle missing values gracefully instead of showing error messages.

4. Keep lookup columns clean, Remove extra spaces, ensure consistent formatting, and avoid mixed data types.

5. Consider alternatives for complex lookups, If you need to look left, match multiple criteria, or handle dynamic column positions, switch to INDEX MATCH or XLOOKUP.

Summary

VLOOKUP is the go-to function for looking up values in Google Sheets. Use it with FALSE for exact matches, wrap it in IFERROR to handle missing data, and switch to INDEX MATCH when you need more flexibility.