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.