VLOOKUP in Google Sheets: Syntax, Examples & Tips

What Is VLOOKUP in Google Sheets?

VLOOKUP stands for Vertical Lookup. It searches down the first column of a range for a value, then returns a result from another column in the same row. Think of it as asking Google Sheets: "Find this item in column A, then tell me what's in column C for that row."

It's one of the most-used functions in Google Sheets, and for good reason. Whether you're matching product IDs to prices, pulling employee data from a roster, or merging information across tabs, VLOOKUP handles it with a single formula.

VLOOKUP Syntax and Parameters

The VLOOKUP function takes four arguments:

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

Critical rule: Always set the fourth parameter to FALSE unless you specifically need an approximate match with sorted data. Leaving it out defaults to TRUE, which silently returns wrong results if your data isn't sorted in ascending order.

Basic VLOOKUP Examples

Exact Match Lookup

Suppose you have a product list in columns A through C: Product ID, Product Name, and Price. To find the price of product ID SKU-205:

=VLOOKUP("SKU-205", A2:C100, 3, FALSE)

This searches column A for the exact text SKU-205, then returns the value from the third column (Price) in that row.

You can also reference a cell instead of hardcoding the search key:

=VLOOKUP(E2, A2:C100, 3, FALSE)

Now whatever value you type in E2 drives the lookup. This makes your sheet interactive.

Approximate Match Lookup

Approximate match is useful for range-based lookups, like assigning letter grades to numerical scores. Given a grading table where column A has the minimum score and column B has the grade:

=VLOOKUP(85, A2:B6, 2, TRUE)

This returns B because 85 falls between 80 and 90. The data must be sorted in ascending order for approximate match to work correctly.

VLOOKUP from a Different Sheet

Cross-sheet lookups are one of the most common VLOOKUP use cases. Reference another sheet by prefixing the range with the sheet name and an exclamation mark:

=VLOOKUP(A2, 'Product Database'!A:C, 3, FALSE)

If the sheet name contains spaces, wrap it in single quotes as shown above. For single-word sheet names, quotes are optional:

=VLOOKUP(A2, Inventory!A:D, 4, FALSE)

Tip: Use absolute references ($) when copying VLOOKUP formulas down a column so the range doesn't shift:

=VLOOKUP(A2, 'Product Database'!$A:$C, 3, FALSE)

VLOOKUP with Multiple Criteria

VLOOKUP only searches one column, but you can work around this limitation by creating a helper column that concatenates multiple values.

Scenario: You need to look up a sales amount by both Region (column A) and Product (column B), with the amount in column C.

Step 1: Insert a helper column (column D) with a concatenation formula:

=A2&"-"&B2

Step 2: Use VLOOKUP against the helper column:

=VLOOKUP("West-Widget", D2:E100, 2, FALSE)

Or with cell references:

=VLOOKUP(F2&"-"&G2, D2:E100, 2, FALSE)

This pattern scales to three or more criteria. Just concatenate additional fields with a delimiter that won't appear in your data.

Wildcard and Partial Match Lookups

VLOOKUP supports two wildcard characters for partial matching when is_sorted is FALSE:

• * matches any sequence of characters
• ? matches exactly one character

To find a product that starts with "Pro":

=VLOOKUP("Pro*", A2:C100, 2, FALSE)

To find a 5-character product code starting with "AB":

=VLOOKUP("AB???", A2:C100, 2, FALSE)

Combine wildcards with cell references using the & operator:

=VLOOKUP("*"&E2&"*", A2:C100, 2, FALSE)

This searches for any cell in column A that contains the value in E2, regardless of what comes before or after it.

Case-Sensitive VLOOKUP

By default, VLOOKUP is case-insensitive. Searching for "apple" matches "Apple", "APPLE", or "aPpLe". If you need case sensitivity, combine INDEX and MATCH with EXACT:

=INDEX(C2:C100, MATCH(TRUE, EXACT(A2:A100, "Apple"), 0))

Press Ctrl+Shift+Enter if you're in older Google Sheets versions that require array entry. In current versions, this formula works as-is.

Handling VLOOKUP Errors

#N/A Error

This is the most common VLOOKUP error. It means the search key wasn't found. Typical causes:

• Extra spaces: The cell looks like "Apple" but actually contains " Apple" or "Apple ". Fix with =VLOOKUP(TRIM(E2), A2:C100, 3, FALSE).
• Data type mismatch: Your lookup value is a number but the column stores it as text (or vice versa). Use VALUE() or TEXT() to convert.
• Wrong range: The search key column isn't the first column in your range.

#REF! Error

The column index exceeds the number of columns in your range. If your range is A2:C100 (3 columns), using an index of 4 triggers this error. Double-check your range and index number.

#VALUE! Error

This occurs when arguments are in the wrong order or the search key exceeds 255 characters. Verify your formula structure matches: search_key, range, index, is_sorted.

Using IFERROR and IFNA

Wrap VLOOKUP in error-handling functions to display friendly messages instead of error codes:

=IFERROR(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "Not found")

IFERROR catches all errors. If you only want to handle missing values while still seeing other errors (which help you debug), use IFNA:

=IFNA(VLOOKUP(A2, Sheet2!A:C, 3, FALSE), "No match")

Best practice: Use IFNA during development so real errors surface. Switch to IFERROR in production sheets shared with non-technical users.

Common VLOOKUP Mistakes and How to Fix Them

Advanced VLOOKUP Techniques

Reverse VLOOKUP (Looking Left)

VLOOKUP only returns values to the right of the search column. To look left, use an array literal to rearrange columns on the fly:

=VLOOKUP("SKU-205", {C2:C100, A2:A100}, 2, FALSE)

The curly braces {C2:C100, A2:A100} create a virtual two-column table where column C becomes the first (search) column and column A becomes the second (result) column. No helper columns needed.

VLOOKUP with ARRAYFORMULA

Instead of dragging VLOOKUP down hundreds of rows, apply it to an entire column at once:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, Sheet2!A:C, 3, FALSE), ""))

This single formula in one cell populates results for every row in column A. Wrap it in IFNA to handle empty rows gracefully.

Two-Way Lookup (VLOOKUP + MATCH)

Make the column index dynamic so users can select which column to return:

=VLOOKUP(A2, Data!A:F, MATCH(E1, Data!1:1, 0), FALSE)

MATCH(E1, Data!1:1, 0) finds the column number of the header in E1, so changing E1 from "Price" to "Stock" automatically adjusts which column VLOOKUP returns. This eliminates hardcoded column numbers that break when columns are inserted or removed.

VLOOKUP vs XLOOKUP vs INDEX MATCH

VLOOKUP isn't the only lookup function in Google Sheets. Here's how it compares to the modern alternatives:

When to use each:

• VLOOKUP: Simple left-to-right lookups where your search key is in the first column. Quick to write and easy to read.
• XLOOKUP: When you need to look left, want built-in error handling, or prefer cleaner syntax. The best choice for new sheets if backward compatibility isn't a concern.
• INDEX MATCH: Maximum flexibility. Works in any direction, handles complex multi-criteria lookups, and performs well on large datasets. Worth learning if you work with spreadsheets daily.

XLOOKUP syntax for comparison:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not found")

The fourth parameter is the value to return when no match is found, removing the need for IFERROR or IFNA wrappers.

Performance Tips for Large Datasets

VLOOKUP can slow down noticeably on sheets with tens of thousands of rows. Here are ways to keep it fast:

• Limit the range: Use A2:C5000 instead of A:C. Open-ended ranges force Google Sheets to scan every row.
• Use sorted data + approximate match: When possible, sort your data and use TRUE as the fourth argument. Approximate match uses binary search, which is dramatically faster on sorted data.
• Replace with XLOOKUP or INDEX MATCH: On very large datasets, these alternatives can outperform VLOOKUP because they search only the lookup column rather than loading the entire range into memory.
• Avoid volatile functions in the search key: Functions like NOW(), TODAY(), or RAND() recalculate on every edit, triggering all dependent VLOOKUPs to recalculate too.

Frequently Asked Questions

Why is my VLOOKUP not working in Google Sheets?

The most common causes are: extra spaces in the search key or data (fix with TRIM()), a data type mismatch between number and text, the search key not being in the first column of the range, or a missing FALSE argument causing approximate match behavior on unsorted data.

Can VLOOKUP return multiple values?

No. VLOOKUP returns only the first match it finds. To return all matching rows, use FILTER instead: =FILTER(B2:C100, A2:A100=E2). This returns every row where column A matches the value in E2.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches vertically (down a column), while HLOOKUP searches horizontally (across a row). Use VLOOKUP when your data is organized in columns and HLOOKUP when your categories are in rows. XLOOKUP handles both directions with a single function.

Can VLOOKUP look at multiple columns?

VLOOKUP searches only the first column of the range. To search across multiple columns, create a helper column that concatenates the values, or switch to INDEX MATCH or XLOOKUP which offer more flexibility.

What happens when VLOOKUP doesn't find a match?

With exact match (FALSE), it returns #N/A. Wrap the formula in IFNA() or IFERROR() to display a custom message like "Not found" instead of the error.

Is VLOOKUP case-sensitive?

No. VLOOKUP treats "apple", "Apple", and "APPLE" as identical. For case-sensitive lookups, use =INDEX(C2:C100, MATCH(TRUE, EXACT(A2:A100, "Apple"), 0)) instead.

Can I VLOOKUP data from another Google Sheets file?

Not directly. Use IMPORTRANGE to pull data from the external file first, then apply VLOOKUP to the imported range. Example: =VLOOKUP(A2, IMPORTRANGE("spreadsheet_url", "Sheet1!A:C"), 3, FALSE). You'll need to authorize access the first time.

Should I replace all my VLOOKUPs with XLOOKUP?

If your sheets are only used in Google Sheets (not shared with Excel users on older versions), XLOOKUP is the better choice for new formulas. It defaults to exact match, supports left lookups, and has built-in error handling. Existing VLOOKUPs that work correctly don't need to be replaced.