IFERROR hides every error. That is dangerous, because it also hides real problems such as a wrong range or a typo in a function name. IFNA catches only #N/A, which is the error a lookup gives when it simply finds nothing.
Syntax
=IFNA(value, value_if_na)
Example with VLOOKUP
=IFNA(VLOOKUP(A2, Prices!A:B, 2, FALSE), "Not listed")
If the product is missing, the cell shows Not listed. If the sheet name is wrong, you still see #REF! and know something is broken.
Why IFERROR can mislead
Imagine a report where IFERROR returns 0 for every error. A broken link makes all sales show 0, and nobody notices. With IFNA the real error stays visible.
Which one to use
- Use IFNA around lookups (VLOOKUP, XLOOKUP, MATCH).
- Use IFERROR only when you really want to hide every kind of error, such as a division by zero in a ratio column.
XLOOKUP has this built in
XLOOKUP has a fourth argument for not found, so =XLOOKUP(A2, Prices!A:A, Prices!B:B, "Not listed") does the same job without a wrapper.
Worked example
=IFNA(VLOOKUP(F2,A2:B100,2,FALSE),"Not found") shows "Not found" when the code in F2 is missing. If the formula had a different error, such as #REF!, it would still show, so you can fix it.
Common mistakes
- Using IFNA for #DIV/0!. It only handles #N/A.
- Using IFNA in Excel 2010 or older. It needs Excel 2013 or later.
- Hiding the missing value with 0 where the 0 gets summed later.
Frequently asked questions
When should I use IFNA instead of IFERROR?
When you expect #N/A from a lookup. IFNA lets other errors show so you can spot real mistakes.
Does IFNA work with XLOOKUP?
XLOOKUP has its own not-found argument, so you rarely need IFNA with it.
What does #N/A mean?
The value was not available, usually a lookup that found no match.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: IFNA, IFERROR, VLOOKUP.
Start Excel practice → Case studies
