How to Use VLOOKUP in Excel (Step by Step, With Examples)

VLOOKUP searches for a value in the leftmost column of a range and returns a value from a column to its right. The formula is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Most beginners set the last argument to FALSE to get an exact match.

A working example

Imagine a price list where column A has product codes and column B has prices:

To find the price of “B200”, you’d write:

=VLOOKUP("B200", A:B, 2, FALSE)

This returns $25 — it found “B200” in column A and returned the value from column 2 of the same row.

The 4 arguments explained

1. lookup_value — what you’re searching for (a cell like E2, or text in quotes).
2. table_array — the range to search, e.g. A:B or A1:B100. The lookup column must be the first column in this range.
3. col_index_num — which column to return from, counting from the left of table_array. 2 = the second column.
4. range_lookup — FALSE for an exact match, TRUE for an approximate match (for sorted number ranges).

Why you get #N/A (and how to fix it)

• The value doesn’t exist — check for typos or trailing spaces.
• It’s in a column to the right — VLOOKUP can only search left to right. The lookup value must be in the leftmost column. (Use INDEX/MATCH or XLOOKUP for right-to-left.)
• range_lookup is TRUE — switch to FALSE unless you truly need approximate matching.
• Number stored as text — wrap with VALUE() to convert.

Using a cell reference instead of text

In real sheets you almost never type the value — you reference a cell:

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

The $ signs lock the range so it doesn’t shift when you copy the formula down a column. This is the #1 cause of “it worked in one row but broke below.”

The modern alternative: XLOOKUP

Newer Excel versions have XLOOKUP, which searches in any direction and handles missing values gracefully:

=XLOOKUP("B200", A:A, B:B)

If you have Excel 2021 or Microsoft 365, XLOOKUP is usually the better choice. VLOOKUP remains everywhere because older files and Google Sheets workflows rely on it — see how to use VLOOKUP in Google Sheets for the Sheets version, and VLOOKUP not working if you hit an error.

VLOOKUP vs. other lookups

FAQ

What is VLOOKUP used for?

Looking up a value in one table (like a product ID) and pulling a matching value from another column (like its price or name). It’s the classic “join two lists” tool.

Why is my VLOOKUP returning #N/A?

Either the value isn’t in the first column, it has a typo/spacing mismatch, or range_lookup is set to TRUE instead of FALSE. See VLOOKUP not working for the full checklist.

Can VLOOKUP look left?

No — the lookup value must be in the leftmost column of your range. Use XLOOKUP or INDEX/MATCH to search right-to-left.

Is XLOOKUP better than VLOOKUP?

Yes, for Excel 2021 and Microsoft 365. XLOOKUP searches any direction, doesn’t need a column index, and has a built-in “not found” message.

Why does my VLOOKUP break when I copy it down?

The range isn’t locked. Use absolute references ($A$2:$B$100) so the range doesn’t shift.

Can I use VLOOKUP across two different sheets?

Yes — reference the other sheet in the range: =VLOOKUP(E2, Sheet2!$A$2:$B$100, 2, FALSE). See Google Sheets formulas for beginners for more cross-sheet basics.