If you've ever needed to look up a value in one column and return a corresponding value from another column, you need VLOOKUP. It stands for "Vertical Lookup" and it's the single most-used formula in Google Sheets.

This guide covers everything: basic syntax, real-world examples, every common error and how to fix it, and when you should graduate to INDEX/MATCH or XLOOKUP instead.

The Syntax

VLOOKUP syntax =VLOOKUP(search_key, range, index, [is_sorted])
  • search_key: The value you're looking for (e.g., a product ID, email, name)
  • range: The table to search in. The first column of this range must contain the search key.
  • index: Which column number to return (1 = first column, 2 = second, etc.)
  • is_sorted: Use FALSE for exact match (almost always what you want), TRUE for approximate match
Rule of thumb

Always use FALSE as the 4th argument. Using TRUE (or leaving it blank) requires your data to be sorted and returns approximate matches, which causes silent wrong results in 90% of use cases.

Basic Example

You have a product list in columns A:C (ID, Name, Price) and want to look up the price for product ID "SKU-042":

Look up product price by SKU =VLOOKUP("SKU-042", A:C, 3, FALSE)

This searches column A for "SKU-042", then returns the value from column 3 (Price) of that row.

To make it dynamic, reference a cell instead of hardcoding the search value:

Dynamic lookup from cell F1 =VLOOKUP(F1, A:C, 3, FALSE)

Real-World Examples

Look up employee department

Employee roster in Sheet1 (columns A:D: ID, Name, Department, Salary). Look up the department for employee ID in cell A2:

Employee department lookup =VLOOKUP(A2, Sheet1!A:D, 3, FALSE)

Match order email to customer name

Cross-reference orders with customers =VLOOKUP(B2, Customers!A:C, 2, FALSE)

Where B2 is the email from your orders sheet, and the Customers sheet has Email in column A, Name in column B.

Look up from another spreadsheet with IMPORTRANGE

VLOOKUP across spreadsheets =VLOOKUP(A2, IMPORTRANGE("spreadsheet_url", "Sheet1!A:D"), 3, FALSE)

Every VLOOKUP Error (and How to Fix It)

#N/A - Value not found

#N/A

The search key doesn't exist in the first column of your range.

Common causes:

  • Extra spaces in the data. Fix: =VLOOKUP(TRIM(A2), range, col, FALSE)
  • Case mismatch. VLOOKUP is case-insensitive by default, but hidden characters can fool it.
  • Number stored as text (or vice versa). Check with =ISNUMBER(A2).
  • The search key genuinely doesn't exist. Wrap in IFERROR:
Handle missing values gracefully =IFERROR(VLOOKUP(A2, Data!A:D, 3, FALSE), "Not found")

#REF! - Index out of range

#REF!

Your index number is larger than the number of columns in your range.

If your range is A:C (3 columns) and you use index 4, you'll get #REF!. Count your columns carefully.

#VALUE! - Wrong argument type

#VALUE!

The index argument isn't a valid number, or the range is invalid.

#ERROR! - Formula parse error

#ERROR!

Syntax issue. Check for missing commas, mismatched parentheses, or wrong quote characters (curly quotes from copy-paste).

VLOOKUP with Multiple Criteria

VLOOKUP only searches one column. To match on multiple criteria (e.g., find the sales amount for "John" in "January"), concatenate a helper column or use ARRAYFORMULA:

Method 1: Helper column in column D Column D formula: =A2&"-"&B2 (creates "John-January") Lookup: =VLOOKUP("John-January", D:E, 2, FALSE)
Method 2: Array formula (no helper column) =ARRAYFORMULA( VLOOKUP( E2&"-"&F2, {A2:A&"-"&B2:B, C2:C}, 2, FALSE ) )

The curly braces { } create a virtual array on the fly: column A+B concatenated, with column C as the return value. No helper column needed.

VLOOKUP vs INDEX/MATCH

INDEX/MATCH does everything VLOOKUP does, plus more. Here's the comparison:

  • VLOOKUP can only look right. The search column must be the leftmost column in your range. INDEX/MATCH can look in any direction.
  • VLOOKUP breaks when you insert columns. Because it uses a column number (index), inserting a column shifts your results. INDEX/MATCH uses a column reference, so it's insert-proof.
  • INDEX/MATCH is faster on large datasets. VLOOKUP scans from left to right. INDEX/MATCH goes directly to the answer.
VLOOKUP equivalent in INDEX/MATCH VLOOKUP version: =VLOOKUP(A2, Products!A:C, 3, FALSE) INDEX/MATCH version: =INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))
Lookup LEFT - impossible with VLOOKUP Data: Column A = Price, Column B = Product Name Find the price for "Widget": =INDEX(A:A, MATCH("Widget", B:B, 0))

VLOOKUP vs XLOOKUP

Google Sheets added XLOOKUP in 2024. It's the modern replacement for VLOOKUP:

XLOOKUP - cleaner syntax =XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

Advantages of XLOOKUP:

  • No column index number. You specify the return column directly
  • Built-in error handling (the 4th argument is the "if not found" value)
  • Can search left, right, bottom-up, or top-down
  • Exact match by default (no need to remember FALSE)

If you're starting fresh, use XLOOKUP. If you're maintaining existing sheets, VLOOKUP still works perfectly.

Performance Tips

  • Use specific ranges, not full columns. =VLOOKUP(A2, B1:D1000, 3, FALSE) is faster than B:D on large sheets.
  • Sort your data + use TRUE if you need approximate matching (e.g., tax brackets, grade boundaries). This uses binary search and is significantly faster.
  • Avoid VLOOKUP inside ARRAYFORMULA for large datasets. It runs once per row. Use INDEX/MATCH or QUERY instead for bulk lookups.
  • Cache with a helper tab. If you're doing 10,000+ lookups against another spreadsheet, IMPORTRANGE the data into a local tab first, then VLOOKUP against the local copy.

When to Use What

  • Simple lookup, search key on the left: VLOOKUP
  • Lookup to the left or dynamic column: INDEX/MATCH
  • New sheets, modern syntax: XLOOKUP
  • Multiple criteria, aggregation: QUERY
  • Return multiple matching rows: FILTER
VLOOKUP gets you 80% of the way. INDEX/MATCH gets you 95%. QUERY gets you to 100%.