Excel guide · 3 min read

#N/A error in Excel: causes and fixes

Quick answer#N/A means "not available": a lookup such as VLOOKUP, XLOOKUP or MATCH could not find the value you asked for. The usual causes are a value that really is missing, extra spaces, or a number being compared with a number stored as text. Fix the data, or wrap the formula in IFNA to show a message instead.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C
1	Code	Item	Price
2	1001	Desk lamp	34.99
3	1002	Monitor arm	89
4	Webcam	Webcam HD	59.95
5	1004	USB hub	24.5

Cause 1: the value is not in the list

The most honest #N/A: code 1009 simply is not in column A. Check spelling and whether the list is complete before you change the formula.

=VLOOKUP("1009",A2:C5,3,FALSE)

Result: #N/A

There is no 1009 in A2:A5, so VLOOKUP returns #N/A.

Fix: show a message for genuinely missing values

When missing values are expected (a new product, a typo from a form), catch the #N/A with IFNA so reports and totals keep working. IFNA only hides #N/A, so other mistakes still show. In Excel 365 and 2021 you can use XLOOKUP's fourth argument instead.

=IFNA(VLOOKUP("1009",A2:C5,3,FALSE),"Not in list")

Result: Not in list

The lookup still fails, but IFNA replaces #N/A with Not in list.

Cause 2: a number looking for text (or the other way round)

These codes were imported as text, so cell A3 holds the text "1002", not the number 1002. To Excel those are different values, and the lookup fails even though you can see 1002 on screen. Text-numbers are usually left-aligned and may show a small green triangle.

=VLOOKUP(1002,A2:C5,3,FALSE)

Result: #N/A

The lookup value is the number 1002 but the column holds text, so the result is #N/A.

Fix: make both sides the same type

Joining an empty string with &"" turns the lookup value into text so it matches. The better long-term fix is to convert the column to real numbers: select it, then Data > Text to Columns > Finish, or click the green triangle and choose Convert to Number. Then the original formula works.

=VLOOKUP(1002&"",A2:C5,3,FALSE)

Result: 89

Now both sides are the text "1002", so the lookup finds the Monitor arm price: 89.

Cause 3: hidden spaces

Cell A4 holds "Webcam " with a trailing space, so a lookup for "Webcam" finds nothing. Spaces sneak in from copy-paste, CSV exports and web forms.

Fix the data rather than the formula: add a helper column with =TRIM(A2), copy it, and paste as values over the original. Web data can also contain non-breaking spaces that TRIM does not remove; use Find and Replace for those.

Insert a helper column next to the codes and enter =TRIM(A2), then fill it down.
Copy the helper column and use Home > Paste > Paste Values over the original codes.
Delete the helper column. Your lookup now matches.

Cause 4: approximate match on unsorted data

If you leave out the last VLOOKUP argument, Excel uses approximate match (TRUE). On an unsorted list that can return #N/A or, worse, a wrong row. Always type FALSE for exact matches. XLOOKUP and MATCH with 0 default to or allow exact match too.

Common mistakes

Hiding every #N/A with IFERROR straight away

You lose the signal that your data has spaces or text-numbers. Find the cause first, then decide whether a fallback message is right.

Lookup range that does not cover the new rows

A2:C5 will not see row 6. Turn the data into a table (Ctrl+T) so references grow automatically.

Using Find to check for the value

Ctrl+F can match partial text and ignore trailing spaces you cannot see. Test with =A4="Webcam" instead, which returns FALSE if anything is different.

FAQ

What does #N/A mean in Excel?

It means a value is not available to the formula. It almost always comes from a lookup function that could not find a match, though you can also type =NA() to mark data as missing on purpose.

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

Usually because of a trailing space, a number stored as text on one side, or a missing FALSE argument. Compare the two cells with =A3=E2 to check if Excel sees them as the same.

How do I sum a column that has #N/A errors?

SUM returns #N/A if any cell in the range is an error. Fix the lookups with IFNA(...,0), or use =AGGREGATE(9,6,range), which sums while ignoring errors.

Functions used

Related guides

Updated October 31, 2026