VLOOKUP #N/A Error in Google Sheets: Causes and Fixes

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 7 min read · includes 2 free exercises

In short: VLOOKUP returns #N/A when it finds no match. The usual causes are a typo or extra space, numbers stored as text, a missing FALSE that leaves approximate match on, or a key that is not in the first column of the range. Fix the cause, then use IFNA to show a clean message.

What #N/A means in VLOOKUP

Google's help page says VLOOKUP returns #N/A when no exact match was found, or when an approximate match was used and the key is smaller than the smallest value in the first column. In plain terms: Sheets looked and could not find your value where it expected it. The fix depends on why.

The example

Here is a product table in cells A1:C5.

ABC
1IDNamePrice
2P-101Mug6.5
3P-102Lamp24
4P-103Backpack39
5P-104Notebook8.75

Cause 1: the key really is not there

A lookup for P-999 returns #N/A because there is no such ID. That is the correct answer. To show a message instead of an error, wrap the formula in IFNA, as Google's page suggests:

=IFNA(VLOOKUP("P-999", A2:C5, 3, FALSE), "Not found")

This returns Not found. IFNA only replaces #N/A, so other errors still show, which makes real mistakes easier to spot than with IFERROR.

Cause 2: extra spaces

A key that looks right can carry a trailing space. Here E2 holds P-103 followed by one space.

E
1Search key
2P-103 

A direct lookup of E2 fails, because "P-103 " is not equal to "P-103". Trim the key:

=VLOOKUP(TRIM(E2), A2:C5, 2, FALSE)

This returns Backpack. Google's page recommends trimming extra spaces. The data in the table can have the same problem, so clean that side too.

Cause 3: numbers or dates stored as text

The number 101 and the text "101" look the same but do not match. Google's page says not to store numbers or dates as text. Convert the column to real numbers, or make both sides the same type.

Cause 4: approximate match left on

If you leave out the last argument, VLOOKUP uses TRUE, an approximate match that needs the first column sorted ascending. On unsorted data it can return wrong values or #N/A. Always add FALSE for exact lookups.

Cause 5: the key is not in the first column

VLOOKUP searches only the first column of the range. If your range starts at column B but the key is in column A, nothing matches. Start the range at the column that holds the key. To look to the left, see how to VLOOKUP to the left.

Other VLOOKUP errors

Try it yourself

Use the tables above. Type your formula into the box, starting with =, then press Check.

Exercise 1: Hide the error

Write a formula that looks up "P-999" in A2:C5 (price, column 3, exact match) and shows Not found instead of #N/A. Use IFNA.

Hint: wrap the whole VLOOKUP in IFNA.

Exercise 2: Fix the stray space

Cell E2 holds P-103 with a trailing space. Write a VLOOKUP that looks it up in A2:C5, returns the Name (column 2), uses an exact match, and trims the key first.

Hint: the search key is TRIM(E2).

What to learn next

Syntax and rules in this guide were checked against Google's VLOOKUP #N/A error help page. See all guides.