#N/A error in Excel: why it appears and how to fix it

What #N/A means

Excel returns #N/A when a function needs a value that is not available. In practice it comes from lookups almost every time: =VLOOKUP(B3,$A$10:$B$14,2,FALSE) searches the first column of the price list for the code in B3, and if no cell matches exactly, the result is #N/A. XLOOKUP, MATCH, HLOOKUP and LOOKUP behave the same way.

The error spreads. Any formula that uses an #N/A cell, such as an order total or a SUM of the column, becomes #N/A as well. That is why one missing code can break a whole report, and why it is worth finding the cause instead of hiding every error.

Worked example: an order sheet with three #N/A lookups

Rows 2 to 7 are orders, rows 10 to 14 the price list. Column C shows the length of each code, column D whether a plain exact-match lookup would find it, and column E the repaired lookup with TRIM and IFNA. The company is fictional.

Worked example: an order sheet with three #N/A lookups

	A	B	C	D	E
1	Order	Product code	LEN	Plain lookup finds it?	Repaired price lookup
2	SO-1201	SN-75	5	Yes	12.40
3	SO-1202	SN-76	6	No: #N/A	14.90
4	SO-1203	CH-12	6	No: #N/A	8.75
5	SO-1204	LP-200	6	No: #N/A	Not listed
6	SO-1205	bx-30	5	Yes	21.50
7	SO-1206	CH-14	5	Yes	9.60
8
9	Price list code	Unit price
10	SN-75	12.40
11	SN-76	14.90
12	CH-12	8.75
13	CH-14	9.60
14	BX-30	21.50
15
16	Orders whose plain lookup returns #N/A				3
17	Orders still "Not listed" after the repair				1
18	Orders priced after the repair (COUNT skips text)				5

All codes and prices are fictional. Text cells keep their spaces, so B3 and B4 hold "SN-76 " and " CH-12".

Cause 1: spaces and other invisible characters

"SN-76 " with a trailing space is not the same text as "SN-76". You cannot see the difference, but LEN can: in the example, the codes in B3 and B4 have 6 characters instead of 5. Clean the lookup value inside the formula:

=VLOOKUP(TRIM(B3),$A$10:$B$14,2,FALSE)

TRIM removes leading and trailing spaces and reduces double spaces to one. Text pasted from a web page can also contain a non-breaking space, CHAR(160), which TRIM leaves alone. SUBSTITUTE(B3,CHAR(160),"") removes it. If the spaces sit in the lookup table instead, clean the table once rather than every formula. For more text clean-up patterns see extracting text between two characters.

Cause 2: numbers stored as text

A customer ID imported as the text "40121" never matches the number 40121 in the customer list, even though both look identical. Signs of text numbers are a small green triangle in the cell, left alignment, and =ISTEXT(H2) returning TRUE for an ID in H2.

Convert the lookup value inside the formula: =VLOOKUP(--H2,…) or =VLOOKUP(VALUE(H2),…). The double minus turns text that looks like a number into a number. In the opposite case, text IDs in the table and numbers in the lookup cell, join an empty string: H2&"". Case does not matter: "bx-30" finds "BX-30", as row 6 of the example shows.

Cause 3: the value really is missing

Sometimes #N/A is correct. Product LP-200 is not in the price list, so no formula can find its price. That is a business fact to report, not a bug to hide. Show it with a clear label:

=IFNA(VLOOKUP(TRIM(B5),$A$10:$B$14,2,FALSE),"Not listed")

Then count the open cases with =COUNTIF(E2:E7,"Not listed"), so someone adds the product or fixes the order. Avoid returning 0 instead of a label: a price of 0 looks like a real value and quietly lowers every total and average that uses it.

IFNA or IFERROR?

IFNA catches only #N/A. IFERROR catches every error: #N/A, but also #REF! from a deleted column, #NAME? from a typo in a function name and #VALUE! from text in arithmetic. Wrapping a lookup in IFERROR therefore hides real bugs behind the friendly label. Use IFNA for lookups, so a broken formula still shows up.

XLOOKUP has the label built in: =XLOOKUP(TRIM(B2),$A$10:$A$14,$B$10:$B$14,"Not listed"). Its fourth argument is returned only when nothing is found. The differences between the two lookups are covered in XLOOKUP vs VLOOKUP.

Other reasons for #N/A

• Approximate match by accident. VLOOKUP without its fourth argument uses approximate match, which needs a sorted first column. On unsorted data it returns wrong prices or #N/A. Always write FALSE (or 0) for exact matches.
• A range that does not move. A lookup table written as A10:B14 without dollar signs shifts when the formula is copied down, and the lower rows search too small a range. Lock it with $A$10:$B$14.
• Lookup value outside the first column. VLOOKUP only searches the leftmost column of the range. Use XLOOKUP or INDEX/MATCH to look to the left, see INDEX MATCH with multiple criteria.
• The #N/A comes from the source. Some models use NA() on purpose to mark missing data, for example so that a chart skips a point.

Find and count #N/A in a sheet

=ISNA(D3) returns TRUE for an #N/A cell, and =SUMPRODUCT(--ISNA(D2:D7)) counts them in a range. To jump to every error at once, use Go To Special › Formulas › Errors. Before an import or a month-end report, a small check cell such as =IF(SUMPRODUCT(--ISNA(D2:D7))=0,"OK","CHECK") makes sure no lookup failed silently.

Common mistakes

• Hiding every error with IFERROR. It also hides #REF! and #NAME?, so a broken formula looks fine. Use IFNA for lookups.
• Returning 0 for a missing value. A 0 price or a 0 rate is a number and distorts totals and averages. Return a text label and count the labels.
• Forgetting the last VLOOKUP argument. Without FALSE, VLOOKUP does an approximate match on unsorted data and returns wrong results or #N/A.
• Cleaning the wrong side. TRIM around the lookup value does not help if the spaces sit in the lookup table. Check LEN on both sides.
• Unlocked lookup ranges. A table reference without $ moves when the formula is copied, and lower rows return #N/A. Lock it with absolute references.
• Assuming a match is case-sensitive. VLOOKUP and MATCH ignore case, so "bx-30" finds "BX-30". If case matters, use EXACT inside an INDEX/MATCH.