IFNA
Returns a value you specify if the formula evaluates to the #N/A error; otherwise returns the result of the formula. Use IFNA to handle only #N/A errors (unlike IFERROR which catches all errors).
IFNA(value, value_if_na)Arguments
valueThe formula or value to check for the #N/A error.value_if_naThe value to return if the formula evaluates to #N/A.
=IFNA(VLOOKUP("Apple",A:B,2,FALSE),"Not found")Returns 'Not found' when VLOOKUP cannot find the value
=IFNA(MATCH("X",A1:A10,0),0)Returns 0 if MATCH fails to find a match
=IFNA(A1,"Missing")If A1 contains #N/A, returns 'Missing'; otherwise returns A1
- •Unlike IFERROR, IFNA only handles the #N/A error — other errors (#VALUE!, #DIV/0!) still propagate
- •Most useful with lookup functions like VLOOKUP, HLOOKUP, MATCH, INDEX/MATCH, XLOOKUP
- •Available since Excel 2013
- •Lets you preserve genuine errors (e.g., #DIV/0!) while only masking lookup failures
- •Using IFNA when the actual error might not be #N/A - IFNA only catches that specific error, so #VALUE!, #DIV/0!, and other error types will still show through unhandled
- •Wrapping an entire complex formula in IFNA purely to hide errors, without investigating why the #N/A appears in the first place, which can mask a genuine data problem
- •Using IFNA where IFERROR would be more appropriate, missing other error types that a lookup formula might realistically produce, like #REF! after a column is deleted
IFERRORCatches any error type, not just #N/A, making it broader but less specific than IFNA.ISNAChecks whether a value is specifically #N/A without substituting a replacement value itself, requiring an additional IF to act on the result.VLOOKUPCommonly wrapped in IFNA, since a lookup with no match is one of the most frequent sources of a #N/A error.What's the difference between IFNA and IFERROR?
IFNA only catches the #N/A error specifically. IFERROR catches any error type, including #VALUE!, #DIV/0!, and #REF!. Use IFNA when you want other error types to remain visible rather than being silently hidden.
Why is IFNA often used with VLOOKUP or XLOOKUP?
A lookup formula returns #N/A when it can't find a match, which is usually an expected, normal outcome rather than a genuine error, making IFNA a natural fit to substitute a friendlier fallback value.
Should I always use IFNA instead of IFERROR?
Not necessarily - IFERROR is better when you want to catch every possible error type. IFNA is more precise when you specifically expect #N/A and want other, more serious errors to remain visible for debugging.
Need to translate a formula using IFNA?
Use our translator to convert your complete formula