IFNA returns your fallback value only when a formula results in the #N/A error; otherwise it returns the formula's result unchanged. =IFNA(VLOOKUP(A2,D:E,2,0),"Not found") replaces a missing-match #N/A with "Not found". It catches no other error type.

IFNA vs IFERROR — which should I use?

Use IFNA when only a missing match (#N/A) is expected — it lets real errors like #REF! and #VALUE! stay visible so bugs aren't buried. Use IFERROR when any error is acceptable. Measured: IFNA catches 1 of 7 error types; IFERROR catches all 7.

Why does IFNA return #NAME?

IFNA was introduced in Excel 2013. Excel 2010/2007 don't recognize it and return #NAME?. Use IFERROR (Excel 2007+) or the classic =IF(ISNA(x),fallback,x) pattern for backward compatibility.

Why does IFNA still show #VALUE! or #REF!?

Because IFNA only replaces #N/A. A bad column index gives #VALUE! (index table width), and those pass through IFNA untouched. Fix the formula, or use IFERROR if you truly want to suppress them.

How do I return a blank instead of 0 with IFNA?

Pass "" explicitly as the second argument: =IFNA(VLOOKUP(...),""). Omitting value_if_na defaults to 0. Note "" is an empty string, not a truly blank cell — ISBLANK returns FALSE and COUNTA counts it.

Does IFNA affect AVERAGE and charts?

Yes, via your fallback. A 0 fallback is included by AVERAGE and plotted on charts (dragging the average down). "" or a text label is skipped by AVERAGE. Choose 0 only when a true zero is meaningful.

Which Excel versions and apps support IFNA?

Excel 2013 and later (2016/2019/2021/2024/Microsoft 365), Google Sheets, recent LibreOffice Calc and Apple Numbers. Excel 2007/2010 return #NAME? — use IFERROR or IF(ISNA(...)) there.

Is IFNA redundant with XLOOKUP?

For XLOOKUP, yes — XLOOKUP has a built-in if_not_found argument: =XLOOKUP(A2,D:D,E:E,"Not found"). Keep IFNA for VLOOKUP and MATCH, which have no native fallback.

IFNA Function in Excel — Syntax, Live Playground & 12 Real Errors Fixed

The IFNA function,
surgical & explained.

Syntax, a live lookup playground, the measured error-catch matrix (IFNA catches only #N/A — IFERROR catches everything), the 0-vs-blank fallback trap, and a sourced error library: hide missing matches without burying real bugs.

LookupLab Research

By LookupLab Research·Updated Nov 15, 2026·Engine: HyperFormula 3.5·What IFNA catches

=IFNA(VLOOKUP(A2,D:E,2,0), "Not found")

error types measured

sourced errors fixed

platforms checked

// 01 — Anatomy

How IFNA works

IFNA inspects a formula's result: if it is the #N/A error, IFNA returns your fallback; otherwise it returns the result unchanged. It is the narrow sibling of IFERROR — built for the one error a lookup legitimately produces when a key is missing.

=IFNA(value, value_if_na)

valueRequired. The formula or cell to check for #N/A — usually a VLOOKUP / MATCH / XLOOKUP that may not find its key.

value_if_naRequired. Returned only when value is #N/A: 0, "" (blank text), or a label like "Not found". Any other error passes through untouched.

vs IFERRORIFERROR(value, value_if_error) catches every error type. IFNA catches only #N/A — use it so real #REF!/#VALUE! bugs stay visible.

// 02 — The catch matrix

What IFNA actually catches

IFNA is the surgical guard: it replaces only #N/A and lets every other error through. The matrix below was measured live in the HyperFormula engine — 6 of 7 error types reproduced directly, #NULL! documented from Microsoft's spec. Click any cell to load that exact error × guard straight into the live playground below.

Error	IFNA	IFERROR	ISNA	ISERROR	ISERR
#N/A	catches	catches	TRUE	TRUE	FALSE
#DIV/0!	#DIV/0!	catches	FALSE	TRUE	TRUE
#VALUE!	#VALUE!	catches	FALSE	TRUE	TRUE
#REF!	#REF!	catches	FALSE	TRUE	TRUE
#NAME?	#NAME?	catches	FALSE	TRUE	TRUE
#NUM!	#NUM!	catches	FALSE	TRUE	TRUE
#NULL! doc	#NULL!	catches	FALSE	TRUE	TRUE

catches / TRUE passes through (error stays) FALSE doc documented, not engine-reproduced

IFNA vs IFERROR — surgical vs blanket

Measured: IFNA catches 1 of 7 error types (only #N/A); IFERROR catches all 7. Wrap a lookup in IFNA and a real #REF! or #VALUE! still surfaces — so genuine bugs are not buried.

=IFNA(VLOOKUP(…,9,0),"safe")#REF!

=IFERROR(VLOOKUP(…,9,0),"safe")"safe"

Same broken column index. IFNA leaves the #REF! visible; IFERROR hides it. Use IFNA when only a missing match is expected.

ISERR vs ISERROR — the #N/A blind spot

test on #N/A	result
`ISNA(#N/A)`	TRUE
`ISERROR(#N/A)`	TRUE
`ISERR(#N/A)`	FALSE

Measured: ISERR is TRUE for every error except #N/A. Use it to flag "real" errors while ignoring expected missing matches — the complement of ISNA.

Pick your fallback: 0 vs "" vs text

fallback	AVERAGE	COUNTA
`0`	counts (drags down)	counts
`""`	skipped	counts
`"Not found"`	skipped	counts

A 0 fallback plots on charts and pulls an AVERAGE down; "" is skipped by AVERAGE but still counts as non-empty for COUNTA. Choose deliberately — "" is not a truly blank cell.

Master error handling

IFNA is the surgical guard. The full course walks the error toolkit end to end — IFNA vs IFERROR vs the IS* tests, choosing fallbacks that don't wreck your averages, and composing guards with lookups so real bugs stay visible.

IFNA · IFERROR · ISNA · ISERR · ISERROR — choosing the right guard

Fallbacks: 0 vs "" vs text, and their effect on AVERAGE / COUNTA / charts

Composing IFNA with VLOOKUP / MATCH / XLOOKUP for real workflows

Backward compatibility: IF(ISNA(...)) for pre-2013 Excel

The IFNA function,
surgical & explained.

How IFNA works

What IFNA actually catches

IFNA vs IFERROR — surgical vs blanket

ISERR vs ISERROR — the #N/A blind spot

Pick your fallback: 0 vs "" vs text

Interactive IFNA playground

The real-world error library

Where IFNA works

Master error handling

Frequently asked

How IFNA works

What IFNA actually catches

IFNA vs IFERROR — surgical vs blanket

ISERR vs ISERROR — the #N/A blind spot

Pick your fallback: 0 vs "" vs text

Interactive IFNA playground

The real-world error library

Where IFNA works

Master error handling

Frequently asked

Related functions