ISERROR vs ISERR vs ISNA in Excel and Google Sheets
ISERR is false for #N/A. An XLOOKUP miss and NA() were FALSE for ISERR here, TRUE for ISERROR and ISNA; #DIV/0! and #VALUE! went the other way.
ISERR is a bad name if you hear “is this an error.” An XLOOKUP miss is an error. I still got FALSE.
=XLOOKUP("nope",K2:K3,L2:L3) → #N/A
=ISERR(B8) → FALSE
=ISERROR(B8) → TRUE
=ISNA(B8) → TRUENA() behaved the same way. So did every other #N/A I generated in this file. ISERR is the function that skips #N/A and reports the other error values. Microsoft’s IS functions page says that in so many words: ISERR is TRUE for any error except #N/A; ISERROR is TRUE for any error, including #N/A; ISNA is TRUE only for #N/A.
I ran the rest of the usual suspects in the same tab so I would not have to trust the documentation alone.
| Formula | Result | ISERR | ISERROR | ISNA | ERROR.TYPE |
|---|---|---|---|---|---|
=1/0 | #DIV/0! | TRUE | TRUE | FALSE | 2 |
=NA() | #N/A | FALSE | TRUE | TRUE | 7 |
=VALUE("x") | #VALUE! | TRUE | TRUE | FALSE | 3 |
=NOTAFUNCTION() | #NAME? | TRUE | TRUE | FALSE | 5 |
=INDEX({1,2,3},1,5) | #REF! | TRUE | TRUE | FALSE | 4 |
=SQRT(-1) | #NUM! | TRUE | TRUE | FALSE | 6 |
XLOOKUP miss | #N/A | FALSE | TRUE | TRUE | 7 |
="" | (blank) | FALSE | FALSE | FALSE | (no type) |
=12 | 12 | FALSE | FALSE | FALSE | (no type) |
A blank string is not an error. A plain number is not an error. I wrapped ERROR.TYPE in IFERROR(...,"none") so the last two rows would not explode; those came back none. I did not test #NULL!, so it is not in the table. Microsoft lists it under ISERROR.
What I actually use these for
The wrappers matter more than the TRUE/FALSE functions on most sheets, and I already compared those in IFERROR vs IFNA. The same cells made the difference obvious again:
=IFERROR(1/0,"caught") → caught
=IFNA(1/0,"caught-na") → #DIV/0!
=IFERROR(NA(),"caught") → caught
=IFNA(NA(),"caught-na") → caught-na
=IFERROR(12,"caught") → 12IFNA left #DIV/0!, #VALUE!, #NAME?, #REF!, and #NUM! alone. It only swallowed the two #N/A cells. IFERROR swallowed everything, including the lookup miss. That is the usual trap: you wanted “if this SKU is missing, say missing,” and you also hid a typo that produced #NAME? or a zero in the denominator. For the #DIV/0! case itself, see DIV/0 Error. For #VALUE!, see VALUE Error.
I reach for ISNA or IFNA when the only “error” I expect is a lookup miss, and I still want other errors to stay visible. I reach for ISERR when I want the opposite test: “this cell is broken, but it is not merely a missing key.” I almost never wrap a formula in ISERROR plus a repeat of the same formula anymore; IFERROR does that job without writing the expression twice. The TRUE/FALSE functions are still useful in a helper column where I need a boolean, not a substitute value.
If several lookup functions are failing at once, start with Lookup Errors rather than sprinkling ISERR on every cell.