How to fix #N/A in Excel
Why a lookup returns #N/A, the usual causes, and how to find which one you have.
What #N/A means
#N/A stands for "not available". Excel shows it when a lookup (VLOOKUP, XLOOKUP, MATCH, XMATCH) could not find the value it was asked for.
Most of the time the formula is fine. The value really is missing, or it is there but does not look the same to Excel as the thing you searched for. Your job is to find out which.
The usual causes
1. The value is not in the list. A new product code, a typo, a customer added last week. This is the honest #N/A: the answer really is "not there".
2. Extra spaces. "A103 " with a space at the end is not the same as "A103". Data copied from other systems often brings spaces with it. Clean it with TRIM:
=XLOOKUP(TRIM(E2), A2:A50, C2:C50)
3. A number stored as text. The list holds the number 1001 and you search for the text "1001", or the other way round. They look identical in the cell but do not match. Text numbers usually sit on the left of the cell and may show a small green triangle. Convert the search value to match the list: VALUE(E2) turns text into a number, TEXT(E2, "0") turns a number into text.
4. The range moved when you copied the formula. Fill =VLOOKUP(E2, A2:C50, 3, FALSE) down one row and the table becomes A3:C51, so the first row drops out. Lock the table with dollar signs: $A$2:$C$50. Press F4 while the cursor is on the reference to add them.
5. An approximate match on an unsorted list. With VLOOKUP's last argument left out, Excel assumes the list is sorted. On an unsorted list it can stop early and give #N/A for a value that is there. Add FALSE at the end, or use XLOOKUP, which matches exactly by default.
Finding which one you have
Pick one row that fails and test it directly. If the lookup value is in E2 and you think the match is in A7:
=E2=A7returnsFALSEwhen the two are different in any way, even if they look the same.=LEN(E2)and=LEN(A7)give different lengths when one of them has hidden spaces.=ISNUMBER(E2)and=ISNUMBER(A7)disagree when one is a number and the other is text.
Once you know the cause, fix the data or the formula, not just the error.
Hiding it versus fixing it
It is tempting to wrap the lookup in IFERROR and move on:
=IFERROR(VLOOKUP(E2, $A$2:$C$50, 3, FALSE), "")
That hides every error, including the ones that mean your formula is broken. A blank cell then looks like a real answer and the mistake travels into your totals.
Hide #N/A only when "not found" is a genuine, expected result, and say so. With XLOOKUP, use its own argument:
=XLOOKUP(E2, $A$2:$A$50, $C$2:$C$50, "Not found")
This replaces only the not-found case. Any other error still shows, so you still hear about real problems.
Common questions
What is the difference between IFNA and IFERROR?
IFNA replaces only #N/A. IFERROR replaces every error, including #REF! and #VALUE!, which usually mean the formula itself is broken. When a lookup may not find a value, IFNA is the safer choice.
Why does my lookup return #N/A when I can see the value?
Usually one of three things: a hidden space at the start or end of one value, a number stored as text in one list and as a number in the other, or VLOOKUP doing an approximate match because its last argument is missing. The checks in "Finding which one you have" tell them apart.
Why does one #N/A break my total?
Any error inside a range passes through SUM, so the total shows #N/A too. Fix the lookups first. If some rows are expected to be missing, =AGGREGATE(9, 6, D2:D50) adds up the range and skips errors.