ISNA
Returns TRUE if the value is the #N/A error value.
ISNA(value)Arguments
valueValue to test
=ISNA(VLOOKUP("X",A:B,2,0))Check if lookup failed
=ISNA(NA())NA() returns #N/A
=IF(ISNA(A1),"Missing",A1)Replace #N/A with text
- •Only detects #N/A, not other errors
- •Use ISERROR for all errors
- •Common with VLOOKUP results
- •Using ISNA to catch every possible error type - it only detects the specific #N/A error, and other errors like #VALUE! or #DIV/0! will pass right through undetected
- •Wrapping an entire formula in ISNA when IFNA or IFERROR would express the intent more directly and concisely, since ISNA only returns TRUE/FALSE rather than substituting a fallback value itself
- •Using ISNA on a formula that might return a different error type depending on the input, missing cases where the actual problem isn't a #N/A at all
IFNADirectly substitutes a fallback value when a formula returns #N/A, rather than ISNA's TRUE/FALSE check that still needs wrapping in IF.ISERRORCatches any error type, not just #N/A, making it broader but less specific than ISNA.IFERRORSubstitutes a fallback for any error type, the broader counterpart to IFNA in the same way ISERROR is the broader counterpart to ISNA.Does ISNA catch all error types?
No, only #N/A specifically. For a broader check covering any error, use ISERROR instead.
Should I use ISNA or IFNA?
IFNA is usually more direct if you just want to substitute a fallback value for #N/A - it combines the check and the replacement in one function, while ISNA only tells you TRUE or FALSE and needs wrapping in IF to actually substitute anything.
Why is my ISNA-based formula not catching an error I'm seeing?
The error is probably a different type, like #VALUE! or #DIV/0!, which ISNA doesn't detect. Use ISERROR if you need to catch any error type.
Need to translate a formula using ISNA?
Use our translator to convert your complete formula