How to use VLOOKUP to find an exact match in Excel

Q: Why does VLOOKUP return #N/A when the value is there?

Usually a hidden space or a number stored as text. Try =VLOOKUP(TRIM(G1),A2:D9,3,FALSE), or compare =G1=A5 to see if Excel thinks they are equal.

Formula (Excel and Google Sheets)

=VLOOKUP(G1,A2:D9,3,FALSE)

Use =VLOOKUP(lookup_value, table, column_number, FALSE). The FALSE at the end makes it look for an exact match.

Result on the example: $449.00 in G2
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.

How it works

VLOOKUP searches the first column of A2:D9 for the SKU in G1 and returns the value from the 3rd column of that table, the price. FALSE asks for an exact match; without it, VLOOKUP assumes the first column is sorted and may return a nearby row.

=VLOOKUP(G1,A2:D9,3,FALSE)

Result449

G1"P-104"
The value in G1.
A2:D98 rows × 4 cols: "P-101", "Desk lamp", $39.99, 140, …
A range: 8 rows × 4 columns.
VLOOKUP(G1,A2:D9,3,FALSE)449
VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.
- lookup_valueG1"P-104"
- table_arrayA2:D98 rows × 4 cols: "P-101", "Desk lamp", $39.99, 140, …
- col_index_num3
- range_lookupFALSE

In Google Sheets

The same formula works in Google Sheets: =VLOOKUP(G1,A2:D9,3,FALSE). We ran it in Google Sheets mode on the same data and it returns $449.00.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=XLOOKUP(G1,A2:A9,C2:C9)	$449.00	Exact match by default, no column number to count, and the result column can be anywhere. Excel 2021, Microsoft 365 and Google Sheets.
=INDEX(C2:C9,MATCH(G1,A2:A9,0))	$449.00	Works in every version and in any direction.

Common mistakes

Leaving out FALSE makes VLOOKUP do an approximate match, which can return a wrong price without any error.
The column number counts from the first column of the table, not from column A of the sheet.
Inserting a column inside the table shifts the column you meant. XLOOKUP and INDEX/MATCH don't have this problem.
#N/A means the SKU wasn't found. Check for extra spaces and for numbers stored as text.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Use VLOOKUP to find an exact match

In G2, look up the price of the SKU in G1.

Questions

Why does VLOOKUP return #N/A when the value is there?

Usually a hidden space or a number stored as text. Try =VLOOKUP(TRIM(G1),A2:D9,3,FALSE), or compare =G1=A5 to see if Excel thinks they are equal.

Can VLOOKUP look to the left?

No, it only returns columns to the right of the first column. Use XLOOKUP or INDEX/MATCH.

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.