Why VLOOKUP Returns #N/A: 6 Causes and How to Test for Each

Last updated: 3 October 2026 · Download: vlookup-na-test-workbook.xlsx, with one sheet per cause, a broken formula and a fixed formula on each.

A VLOOKUP that returns #N/A is telling you one specific thing: it searched the first column of your table and found no value that is exactly identical to the one you gave it. That is all #N/A means. It is not a syntax error, and Excel is not broken.

The hard part is that "exactly identical" is stricter than it looks. Two cells can display the same characters and still be different values. This guide covers the six situations that cause almost every #N/A. Each has a quick test you can run in your own workbook and a fix. Every example is reproduced in the downloadable workbook, so you can see the broken version and the fixed version side by side.

Before you start: the two-cell test

When a lookup fails but you can see the value in the table, pick the lookup cell and the table cell you expect it to match, and run these checks next to them:

• =LEN(A2) and =LEN(H8). If the lengths differ, one of them contains extra characters you can't see.
• =ISNUMBER(A2) and =ISNUMBER(H8). If one is TRUE and the other FALSE, one is a number and the other is text.
• =A2=H8. This returns FALSE if the values are not identical. (This comparison ignores case, and so does VLOOKUP.)

Those three checks diagnose causes 1, 2 and 5 below in a few seconds.

Cause 1: a trailing or leading space

Sheet "1 Spaces" in the workbook.

The lookup cell contains P-103 with a space after the 3, typed by accident or left over from an export. The table contains P-103. VLOOKUP with FALSE compares every character, so the two values don't match:

=VLOOKUP(A6,$H$6:$I$10,2,FALSE)        → #N/A
=VLOOKUP(TRIM(A6),$H$6:$I$10,2,FALSE)  → USB hub

Test: =LEN(A6) returns 6, while the table cell returns 5.

Fix: wrap the lookup value in TRIM. If the spaces are in the table rather than the lookup value, TRIM around the lookup value won't help. Clean the table column instead: add a helper column with =TRIM(H6), fill it down, copy it, and use Paste Special › Values over the original.

Cause 2: a number stored as text

Sheet "2 Text numbers".

This is the most common cause with data imported from CSV files, accounting systems, or web forms. The table holds product ID 1003 as a number. The lookup cell holds "1003" as text, perhaps entered with a leading apostrophe or imported that way. They display identically, but Excel does not treat text and numbers as equal when it looks something up.

=VLOOKUP(A6,$H$6:$I$10,2,FALSE)         → #N/A
=VLOOKUP(VALUE(A6),$H$6:$I$10,2,FALSE)  → USB hub

Test: =ISNUMBER(A6) returns FALSE and =ISNUMBER(H8) returns TRUE. Excel often also shows a small green triangle in the corner of a number stored as text, and text aligns left by default while numbers align right.

Fix: convert whichever side is "wrong" so both are the same type:

• If the lookup value is text and the table holds numbers, use VALUE(A6) or --A6.
• If the lookup value is a number and the table holds text, use A6&"" or TEXT(A6,"0").
• To convert a whole column of text numbers once, select it and use Data › Text to Columns › Finish. Excel re-reads each cell and turns text numbers into real numbers.

Watch out for IDs with leading zeros, such as 00417. Converting those to numbers drops the zeros. In that case, keep both sides as text.

Cause 3: the value isn't in the first column of the range

Sheet "3 First column".

VLOOKUP only ever searches the leftmost column of the range you give it. In the example, the table is laid out as Product, Code, Price, and the formula looks up a code using a range that starts at Product:

=VLOOKUP(A6,$H$6:$J$10,3,FALSE)   → #N/A  (searches the Product column)
=VLOOKUP(A6,$I$6:$J$10,2,FALSE)   → 45    (range now starts at Code)

When you move the start of the range, the column index changes too. Price was column 3 of H:J and is column 2 of I:J. Forgetting to update that number is a common follow-on mistake.

Test: =ISNUMBER(MATCH(A6,$I$6:$I$10,0)) returns TRUE when the code is in column I. If this is TRUE but the VLOOKUP still fails, the range is starting in the wrong column.

Fix: start the range at the column you're searching, or switch to INDEX/MATCH, which doesn't care where the columns are:

=INDEX($J$6:$J$10,MATCH(A6,$I$6:$I$10,0))   → 45

INDEX/MATCH can also return a column to the *left* of the search column, which VLOOKUP cannot do at all. The Lookup Function Chooser helps you pick between VLOOKUP, INDEX/MATCH and XLOOKUP based on your Excel version and table layout.

Cause 4: the table range isn't locked

Sheet "4 Unlocked range".

This one is easy to miss because the first rows work. The formula in B6 was written as:

=VLOOKUP(A6,H6:I10,2,FALSE)

and then filled down. Without $ signs, Excel shifts the range by one row for each row you fill. In row 9 the formula has become =VLOOKUP(A9,H9:I13,2,FALSE). The table now starts at row 9, so any code stored above row 9 can't be found:

Test: click a failing cell and look at the formula bar. If the range reference has changed from the first row's, this is the cause.

Fix: lock the range with $H$6:$I$10. In the formula bar, select the range and press F4 to add the $ signs. A cleaner fix is to format the table as an Excel Table (Ctrl+T) and refer to it by name. A table reference doesn't shift, and it grows when you add rows.

Cause 5: a non-breaking space from web or PDF data

Sheet "5 Web spaces".

If you fixed cause 1 with TRIM and the lookup *still* fails, you're probably dealing with this. Text copied from web pages, PDFs and some exports often contains a non-breaking space, which is character 160. It looks exactly like an ordinary space (character 32), but it's a different character, and TRIM only removes character 32.

=VLOOKUP(A6,$H$6:$I$10,2,FALSE)                                → #N/A
=VLOOKUP(TRIM(A6),$H$6:$I$10,2,FALSE)                          → #N/A
=VLOOKUP(TRIM(SUBSTITUTE(A6,UNICHAR(160)," ")),$H$6:$I$10,2,FALSE)  → Monitor arm

Test: =UNICODE(RIGHT(A6,1)) returns the character code of the last character. 160 means a non-breaking space, and 32 means an ordinary space that TRIM can handle. For a space at the start of the text, use LEFT instead of RIGHT.

Fix: replace character 160 with a normal space, then TRIM. On Windows, CHAR(160) works the same as UNICHAR(160). UNICHAR also works on Mac and in Excel for the web. To clean a whole column without formulas, use Find & Replace: in the Find box, hold Alt and type 0160 on the numeric keypad, leave Replace empty, and choose Replace All.

Cause 6: the value really isn't there

Sheet "6 Truly missing".

Sometimes #N/A is correct: you looked up P-999 and it isn't in the price list. The question then isn't how to fix the formula but how to show the result. A column of #N/A makes a report hard to read, and it breaks any SUM over that column.

=VLOOKUP(A6,$H$6:$I$10,2,FALSE)                               → #N/A
=IFNA(VLOOKUP(A6,$H$6:$I$10,2,FALSE),"Not in price list")     → Not in price list

Use IFNA, not IFERROR. IFERROR hides *every* error, including #REF! from a deleted column and #VALUE! from a broken argument. Those errors point to real mistakes you'd want to see. IFNA only catches #N/A, the one error that means "not found". It works in Excel 2013 and later.

Only add IFNA *after* you've ruled out causes 1 to 5. Wrapping a lookup in IFNA while it's still failing for one of those reasons just replaces a visible problem with a misleading message.

A related trap: no error, but the wrong answer

If you leave off the last argument of VLOOKUP, or set it to TRUE, Excel performs an approximate match. That mode assumes the first column is sorted in ascending order and returns the closest smaller value rather than requiring an exact match. On unsorted data it can return a value from the wrong row without showing any error at all. It returns #N/A only when the lookup value is smaller than everything in the first column.

For codes, IDs, names and anything else that must match exactly, always end VLOOKUP with FALSE (or 0). Use approximate match only on purpose, for example for tax bands or grade boundaries in a sorted table.

Checklist

When VLOOKUP returns #N/A, work through these in order:

1. Run the two-cell test: LEN, ISNUMBER, and =A2=H8.
2. Different lengths? Check for spaces (cause 1), then character 160 (cause 5).
3. One is a number and one is text? Convert so both match (cause 2).
4. Is the value in the first column of the range? (cause 3)
5. Did the range shift when the formula was filled down? (cause 4)
6. All fine, and the value genuinely isn't there? Use IFNA to show a clear message (cause 6).

If the error isn't #N/A, the Excel Error Decoder explains #VALUE!, #REF!, #NAME?, #DIV/0! and #SPILL!. To read a long lookup formula someone else wrote, paste it into the Formula Explainer to see each part in the order Excel calculates it.

FAQ

Why does VLOOKUP return #N/A when I can see the value in the table? The two values aren't identical, even though they look the same. The usual differences are a trailing space, a non-breaking space, or a number stored as text on one side. Compare LEN and ISNUMBER on both cells to find out which.

Does VLOOKUP care about upper and lower case? No. VLOOKUP treats p-103 and P-103 as the same value. Case is never the cause of #N/A.

Should I switch to XLOOKUP? XLOOKUP can search any column and has a built-in "if not found" argument, but it's only available in Excel 2021, Microsoft 365 and Excel for the web. Causes 1, 2 and 5 affect XLOOKUP and INDEX/MATCH in exactly the same way, because they're about the data rather than the function.

Is IFERROR or IFNA better for hiding #N/A? IFNA. It only hides the "not found" result. IFERROR also hides errors caused by real mistakes in the formula.

Does the test workbook work in Google Sheets? Yes. Upload it to Google Drive and open it with Google Sheets. All of the formulas in it are supported.