IFERROR vs IFNA: Which Errors to Catch

Excel & Google Sheets

How it works

IFNA is the surgical option: it replaces only #N/A — the error lookups return when the value genuinely isn't there — and lets every other error through untouched. That matters because #NAME? means you misspelled the function, #REF! means a deleted column, and #DIV/0! means broken math; those are bugs to fix, not results to relabel. IFERROR replaces all of them with your fallback, so a typo like =IFERROR(VLOKUP(...),"Not on list") quietly reports every row as "Not on list" and nobody notices the formula is broken. The verified grid below shows the difference directly: both handle a missing name identically, but feed them a divide-by-zero and IFNA passes the #DIV/0! through while IFERROR papers over it. Use IFERROR only when you've deliberately decided any failure should show the fallback.

VLOOKUP(D2,A2:B10,2,FALSE)

The lookup that returns #N/A when D2 isn't in column A.

"Not on list"

Shown only for #N/A. Any other error still surfaces, so real bugs stay visible.

When to use it

Wrap IFNA around VLOOKUP, XLOOKUP, INDEX MATCH, and MATCH anywhere a missing value is normal — new customers, unmapped SKUs. Save IFERROR for the rare cell where every possible error genuinely means the same fallback.

Common mistakes

• ✕Blanket IFERROR around every formula.

  ✓IFERROR turns a misspelled function or a deleted column into your innocent-looking fallback text, workbook-wide. Wrap lookups in IFNA instead, and fix non-#N/A errors at the source.

• ✕Expecting IFNA to catch #VALUE! or #DIV/0!.

  ✓IFNA passes those straight through — by design. If a lookup shows #VALUE!, the problem is inside the lookup (wrong argument types), not a missing value. Fix the formula rather than switching to IFERROR.

• ✕Using "" as the fallback and breaking downstream math.

  ✓IFNA(...,"") leaves invisible empty text that COUNTA still counts and arithmetic chokes on. Prefer a visible label like "Not on list", or 0 when the column feeds a SUM.