Jun 2, 2026

How to Use VLOOKUP in Excel (with Examples, 2026)

Excel Vlookup Spreadsheets How-to Formulas Productivity

VLOOKUP (“vertical lookup”) finds a value in the first column of a table 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(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value — the value you’re looking for (e.g., a product ID or a name).
table_array — the block of cells to search. The lookup value must be in the first (leftmost) column of this range.
col_index_num — the column number within the table (counting from 1) whose value you want returned.
range_lookup — FALSE for an exact match (what you almost always want), TRUE for an approximate match (requires the first column 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 table (prices)
FALSE — exact match

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

2. Look Up Data From Another Sheet

To pull data from a different worksheet, 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 workbook, point at the open file in square brackets, like [Sales.xlsx]Products!A2:B100.

3. Lock the Range When Copying Down

If you’ll copy the formula down a column, anchor the table 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. Press F4 after selecting a range to add the dollar signs automatically.

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 lookup value, and requires the first column sorted ascending. Useful for bracket lookups (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 IFERROR:

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

Use IFNA instead of IFERROR if you want to catch only #N/A and still surface other errors.

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 lookup value actually exists in the first column of the table.

#REF! error

Your col_index_num is larger than the number of columns in the table (e.g., index 3 on a 2-column range). Widen the table 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 a different approach.

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 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