The Excel IFNA function is the precision version of IFERROR: it replaces exactly one error — #N/A, the “value not found” signal — and lets every other error through untouched. That makes it the right wrapper for VLOOKUP, XLOOKUP, and MATCH: expected misses become friendly text, while genuine formula bugs stay loudly visible so you can fix them.
Syntax
| Argument | Description | |
|---|---|---|
value | Required | The formula to evaluate — typically a lookup that might not find its target. |
value_if_na | Required | What to return when value results in #N/A. Any other error passes through unchanged. |
Available in: Excel 2013 and every version since, including Excel for Microsoft 365 and Excel for the web. Excel 2010 and earlier show #NAME? — there, the closest substitute is IF(ISNA(…), …).
Wrap a lookup the right way
A price list sits in A2:B4; order SKUs arrive in column D, including one that isn’t listed. IFNA turns the expected miss into readable text:
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | SKU | Price | Order SKU | Price | |
| 2 | AX-100 | 24.00 | AX-100 | 24.00 | |
| 3 | BX-220 | 31.50 | ZZ-999 | Not listed | |
| 4 | CX-310 | 18.75 | CX-310 | 18.75 |
The same pattern works with MATCH and XLOOKUP — though note XLOOKUP also has its own built-in if_not_found argument, which does the same job one layer closer:
Try it: interactive IFNA demo
Pick what the inner formula produces and compare what IFNA and IFERROR each hand back. Only one of them lets bugs stay visible.
IFNA vs IFERROR: why narrower wins for lookups
Both functions have the same shape; the difference is the size of the net. A lookup has exactly one expected failure — the value isn’t there, #N/A. Everything else means the formula is broken:
With the IFERROR version, deleting column B doesn’t raise an alarm — every row just says “Not listed” and the report looks plausible while being completely wrong. With IFNA, the same accident shows #REF! in every row and gets fixed in minutes.
Rule of thumb: wrapping a lookup? Reach for IFNA first. Reach for IFERROR only when any error genuinely means the same thing — like a division where #DIV/0! is the only realistic failure.
Pitfalls & gotchas
#NAME? — Excel 2010 or earlier. IFNA arrived in Excel 2013. In older versions use =IF(ISNA(formula), "fallback", formula) — clumsier, since the formula is written (and evaluated) twice.
Pitfall: expecting IFNA to catch other errors. By design it doesn’t. If your divide-by-zero or #VALUE! is showing through, that’s the function working as intended — fix the underlying problem, or consciously decide IFERROR’s wider net is appropriate.
Pitfall: text that looks like an error. Cells containing the typed text “N/A” are not the #N/A error value — IFNA ignores them. Only a genuine #N/A error triggers the fallback.
Pitfall: doubling up with XLOOKUP. IFNA(XLOOKUP(…), "x") works, but XLOOKUP’s own if_not_found argument already covers the not-found case. Use the built-in argument and save a function call — keep IFNA for VLOOKUP, HLOOKUP, and MATCH, which lack one.
Practice workbook
Frequently asked questions
What's the difference between IFNA and IFERROR?
Which Excel versions support IFNA?
Should I use IFNA with XLOOKUP?
if_not_found argument that handles the missing-value case directly: =XLOOKUP(D2, A:A, B:B, "Not listed"). IFNA earns its keep with VLOOKUP, HLOOKUP, and MATCH, which have no such argument.Why is my IFNA formula still showing an error?
Can IFNA return a blank cell?
=IFNA(VLOOKUP(…), ""). As with IFERROR, the result is an empty text string, not a true blank — fine for display, but it will trip arithmetic and COUNTA. Return 0 if the cell feeds calculations.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class