How to replace #N/A errors in Excel
#N/A means a lookup couldn’t find what it was looking for. It’s useful information, but it looks alarming in a report.
XLOOKUP has a built-in “if not found” argument. For older lookups, wrap them in IFNA or IFERROR.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Code | Product | Price |
| 2 | P-10 | Lamp | 45 |
| 3 | P-20 | Desk | 210 |
| 4 | P-30 | Chair | 120 |
=XLOOKUP("P-99",A2:A4,C2:C4,"Not found")Result: Not found
P-99 isn’t in the list, so the formula shows the message instead of #N/A.
Step by step
- Write your lookup as usual.
- For XLOOKUP, add the message as the fourth argument.
- For VLOOKUP, wrap it: =IFNA(VLOOKUP(…),"Not found").
Other ways to write it
=IFNA(VLOOKUP("P-99",A2:C4,3,FALSE),"Not found")IFNA only catches #N/A, so other mistakes still show up.=IFERROR(XLOOKUP("P-99",A2:A4,C2:C4),"Not found")IFERROR catches every error, which can hide real problems.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Hiding every error
- IFERROR also hides typos and broken references. Prefer IFNA or XLOOKUP’s own argument.
- Returning 0
- Showing 0 for a missing item can be mistaken for a real price. A clear message is safer.
Reading isn’t the same as doing
Handling errors and gaps gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.