Excel IFNA Function: Catch Only #N/A and Let Real Errors Show
Runs a formula and returns your own value only if it produces #N/A.
IFNA is IFERROR with a narrower net. IFERROR catches every error there is; IFNA catches only #N/A and lets everything else through. That sounds like a small distinction and it is the difference between a sheet that hides its problems and one that does not.
The case it is built for is a lookup that might legitimately miss. A VLOOKUP for a product that is genuinely not in the catalogue returns #N/A, and replacing that with "Not stocked" is exactly right. But if the same formula's range reference breaks, it returns #REF!, and IFERROR would replace that with "Not stocked" too — reporting a broken formula as a missing product.
So the rule is simple. Use IFNA around lookups, where a miss is expected and any other error means something is wrong. Use IFERROR only where you genuinely want every failure handled the same way, which is rarer than its popularity suggests.
Syntax
=IFNA(value, value_if_na)Arguments
valueRequired- The formula to attempt. Usually a lookup of some kind.
value_if_naRequired- What to return if it produces #N/A. Any other error passes through untouched.
The example data
A horizontally laid-out table: labels across row 1, data in row 2 and row 3.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Metric | Q1 | Q2 | Q3 | Q4 |
| 2 | Revenue | 48000 | 52500 | 61000 | 74500 |
| 3 | Headcount | 12 | 12 | 14 | 17 |
Worked examples
=IFNA(HLOOKUP("Q5", A1:E3, 2, FALSE), "No data")Result: No data
Q5 does not exist in the table, so the #N/A becomes something a reader can act on.
=IFNA(HLOOKUP("Q3", A1:E3, 2, FALSE), "No data")Result: 61000
When the lookup works, IFNA is invisible and returns the result unchanged.
=IFNA(HLOOKUP("Q3", A1:E3, 9, FALSE), "No data")Result: #REF!
The point of the function. Row 9 does not exist, which is a broken formula rather than a missing value, and IFNA lets it show. IFERROR would have hidden it.
=IFNA(HLOOKUP("Q5", A1:E3, 2, FALSE), 0)Result: 0
Returning 0 rather than text, so the column can still be summed downstream.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
A #VALUE! or #REF! is still showing
Why it happens: That is IFNA working correctly — it only catches #N/A.
How to fix it: Fix the underlying formula. If you genuinely want every error caught, IFERROR is the right function, but be sure that is what you mean.
#NAME?
Why it happens: IFNA arrived in Excel 2013 and older versions do not have it.
How to fix it: Use =IF(ISNA(formula), fallback, formula), which works everywhere but evaluates the formula twice.
Totals change after wrapping
Why it happens: The fallback is text sitting in a numeric column, so SUM skips those rows.
How to fix it: Return 0 rather than text where the column feeds arithmetic.
Tips worth knowing
- Prefer IFNA to IFERROR around any lookup. It is the same length to type and strictly safer.
- XLOOKUP's fourth argument does this natively, so a modern lookup rarely needs wrapping at all.
- Wrap the lookup itself, not a whole chain of arithmetic — a wide wrapper hides more than you intended.
- If you find yourself wrapping everything in IFERROR out of habit, that habit is the bug.
Frequently asked questions
What is the difference between IFNA and IFERROR?
IFERROR catches every error type — #N/A, #REF!, #VALUE!, #DIV/0!, #NAME?, #NUM! and #NULL!. IFNA catches only #N/A. For lookups, IFNA is almost always the right choice: a missing value is expected, but a #REF! means the formula itself is broken and you need to see it.
Do I still need IFNA with XLOOKUP?
Usually not. XLOOKUP takes a not-found value as its fourth argument, which handles the same case without a wrapper. IFNA is still useful around VLOOKUP, INDEX/MATCH and anything else that has no built-in fallback.
Which version of Excel has IFNA?
Excel 2013 and later. In older versions the equivalent is =IF(ISNA(formula), fallback, formula), which works identically but evaluates the formula twice and is correspondingly slower on large models.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.