ISERROR、ISERR、ISNA 的区别
ISERR 这个名字很像“是否是错误”，但对 XLOOKUP 未命中得到的 N/A，本表里 ISERR 为 FALSE，ISERROR 和 ISNA 为 TRUE。NA() 也一样。微软对 IS 函数的说明是：ISERR 对除 N/A 以外的错误为 TRUE，ISERROR 对所有错误为 TRUE，ISNA 只对 N/A 为 TRUE。
快速解答
ISERR 这个名字很像“是否是错误”，但对 XLOOKUP 未命中得到的 N/A，本表里 ISERR 为 FALSE，ISERROR 和 ISNA 为 TRUE。NA() 也一样。微软对 IS 函数的说明是：ISERR 对除 N/A 以外的错误为 TRUE，ISERROR 对所有错误为 TRUE，ISNA 只对 N/A 为 TRUE。
=XLOOKUP("nope",K2:K3,L2:L3) → #N/A
=ISERR(B8) → FALSE
=ISERROR(B8) → TRUE
=ISNA(B8) → TRUE检查要点
同一张表里，1/0 得到 DIV/0，VALUE("x")、不存在的函数名、INDEX 越界、SQRT(-1) 分别得到 VALUE、NAME、REF、NUM。这些 ISERR 和 ISERROR 都为 TRUE，ISNA 为 FALSE。ERROR.TYPE 依次为 2、3、5、4、6；两处 N/A 为 7。空文本和数字 12 三种判断都是 FALSE。我没有测试 NULL，所以表里没有这一行。
边界与兼容性
同样这些单元格上，IFERROR 会接住所有错误，包括查找未命中；IFNA 只接住两处 N/A，DIV/0、VALUE、NAME、REF、NUM 仍会显示出来。只想处理“找不到键”、同时保留其他错误时，用 ISNA 或 IFNA。想检测“坏了，但不是单纯缺键”时，才用 ISERR。空文本和 12 不是错误，IFERROR(12,"caught") 仍返回 12。
完整英文示例、公式与作者备注
Excel ComparisonISERR 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.