May 26, 2026

How to Use VLOOKUP in Google Sheets (with Examples, 2026)

Google Sheets Vlookup Spreadsheets How-to Formulas Productivity

VLOOKUP (“vertical lookup”) finds a value in the first column of a range and returns a value from another column in the same row — the fastest way to pull matching data from one table into another. Here’s the syntax, a worked example, and fixes for the errors that trip everyone up.

VLOOKUP Syntax

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

search_key — the value you’re looking for (e.g., a product ID or a name).
range — the block of cells to search. The lookup value must be in the first (leftmost) column of this range.
index — the column number within the range (counting from 1) whose value you want returned.
is_sorted — FALSE for an exact match (what you almost always want), TRUE for an approximate match (requires the first column to be sorted ascending).

1. A Worked Example

Say column A has product IDs and column B has prices (in A2:B100), and you want the price for the ID in cell D2:

=VLOOKUP(D2, A2:B100, 2, FALSE)

D2 — the ID to look up
A2:B100 — the table to search
2 — return the value from the 2nd column of the range (prices)
FALSE — exact match

Press Enter. Sheets finds the matching ID in column A and returns its price.

2. Look Up Data From Another Sheet

To pull data from a different tab, prefix the range with the sheet name and an exclamation mark:

=VLOOKUP(D2, 'Products'!A2:B100, 2, FALSE)

Wrap the sheet name in single quotes if it contains spaces (e.g., 'Price List'!A2:B100). To reference an entirely separate spreadsheet file, combine VLOOKUP with IMPORTRANGE.

3. Lock the Range When Copying Down

If you’ll copy the formula down a column, anchor the range with $ so it doesn’t shift:

=VLOOKUP(D2, $A$2:$B$100, 2, FALSE)

The $A$2:$B$100 stays fixed while D2 moves to D3, D4, and so on as you fill down.

4. Exact vs. Approximate Match

FALSE (exact) — returns only a perfect match, otherwise #N/A. Use this for IDs, emails, SKUs — anything that must match precisely.
TRUE (approximate) — returns the closest value less than or equal to the search key, and requires the first column to be sorted ascending. Useful for bracket lookups (e.g., tax tiers, grade bands). If the column isn’t sorted, TRUE returns wrong answers silently.

When in doubt, use FALSE.

5. Wrap It to Avoid #N/A

To show a friendly message instead of #N/A when there’s no match, wrap VLOOKUP in IFNA:

=IFNA(VLOOKUP(D2, A2:B100, 2, FALSE), "Not found")

6. Troubleshooting

#N/A error

No match was found. Check for: extra spaces (use TRIM), a number stored as text vs. a real number, or a typo. Confirm the search key actually exists in the first column of the range.

#REF! error

Your index is larger than the number of columns in the range (e.g., index 3 on a 2-column range). Widen the range or lower the index.

VLOOKUP only returns the first match

VLOOKUP returns the first match top-to-bottom. If you have duplicates and need a different one, sort/filter first or use QUERY instead.

It can’t look to the left

VLOOKUP can only return columns to the right of the lookup column. To look left, use INDEX/MATCH or XLOOKUP (which can search in any direction).

Wrong results with TRUE

Approximate match needs the first column sorted ascending. Either sort it or switch to FALSE.

Ready to automate your busywork?

Carly schedules, researches, and briefs you—so you can focus on what matters.

Get Carly Today →

Or explore our free tools