IFERROR 與 IFNA：查詢時該用哪一個？

The same split showed up for #DIV/0!, #VALUE!, and an out-of-range INDEX: IFNA left the error in the cell; IFERROR replaced all of them with the fallback text.

=IFNA(XLOOKUP(F2, A2:A100, C2:C100), "Not in catalog")

XLOOKUP already has a built-in “if not found” argument, so you often do not need either wrapper:

=XLOOKUP(F2, A2:A100, C2:C100, "Not in catalog")

Tested in: Live formulas in Google Sheets, November 12, 2026. Microsoft’s IFERROR and IFNA documentation is cited for Excel availability. This is not a new desktop Excel workbook test on this date.

Which versions this applies to

• Excel for Microsoft 365, Excel 2021, 2019, 2016 and 2013: Both IFERROR and IFNA are available. Microsoft’s IFNA documentation lists Excel 2013 as the first version.
• Excel 2010: IFERROR is available (it arrived in Excel 2007). IFNA is not. If a file has to open in Excel 2010, you cannot use IFNA there.
• Google Sheets: Both functions are available and were checked for this article.

What each function actually catches

IFNA has one job: if the first argument is #N/A, return the second argument; otherwise return the first argument unchanged, errors included.

IFERROR has a broader job: if the first argument is any error, return the second argument.

That difference is the whole decision. A lookup #N/A usually means “this key is not in the list” (or the two values are not exactly equal). A #REF! or #NAME? means the formula should not be trusted. Hiding those last two behind the same “Not found” label is how a typo survives in a report.

Lookups: the case that favors IFNA

A missing SKU produced #N/A from XLOOKUP. Both wrappers replaced it, as they should:

=IFNA(XLOOKUP("SKU-999", A2:A3, C2:C3), "missing")
=IFERROR(XLOOKUP("SKU-999", A2:A3, C2:C3), "hidden")

When the SKU existed, both returned the real price, 12.5, so the wrapper is idle on a successful match.

The problem appears when the lookup is malformed. =VLOOKUP("SKU-101", A2:B3, 5, FALSE) asked for a fifth column of a two-column range and returned #REF!. IFNA left #REF! in the cell. IFERROR replaced it with hidden. A one-letter typo, XLLOOKUP, returned #NAME?. IFNA left that visible too; IFERROR hid it.

If your “not found” label is sitting on top of a #NAME?, you will think the SKU is missing when the function name is wrong, or when the file has been opened in an Excel version that does not have XLOOKUP. That is a long troubleshooting detour. The wider checklist is in Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide.

VLOOKUP and INDEX/MATCH have no built-in “if not found” argument, so IFNA is the wrapper to use there:

=IFNA(VLOOKUP(F2, A2:C100, 3, FALSE), "Not in catalog")
=IFNA(INDEX(C2:C100, MATCH(F2, A2:A100, 0)), "Not in catalog")

Pass FALSE or 0 for exact match. Approximate match can return a neighbor’s value with no error at all, and neither IFNA nor IFERROR will catch that. See VLOOKUP Returning the Wrong Value? and the comparison in XLOOKUP vs INDEX MATCH vs VLOOKUP.

Errors that are not “not found”

The same pattern held for other error types in this test:

• =1/0 returned #DIV/0!. IFNA kept #DIV/0!. IFERROR returned hidden.
• ="A"+1 returned #VALUE!. IFNA kept it. IFERROR hid it.
• =FIND("z","Apple") also returned #VALUE! (FIND does not use #N/A when the text is missing). IFNA kept #VALUE!. IFERROR hid it.
• An empty FILTER, =FILTER({1;2},{FALSE;FALSE}), returned #N/A. Both wrappers replaced it, because this one really is #N/A.
• =INDEX(M2:M3, 20) on a two-row range returned #NUM! in this Sheets file; =INDEX({10;20;30}, 20) on a literal array returned #REF!. IFNA left both errors visible. IFERROR hid both.

So IFERROR is not “safer.” It is quieter. Use it when you have a calculation that can fail in several expected ways and you have already decided that any failure should show the same fallback — for example, a survey field that might be blank, text, or zero, where a single display value is acceptable. Do not use it as the default wrapper on a lookup you still need to debug.

XLOOKUP’s own argument vs a wrapper

XLOOKUP’s fourth argument is only for the not-found case, which is the same idea as IFNA:

=XLOOKUP(F2, A2:A100, C2:C100, "Not in catalog")

If the function name is typo’d, you still get #NAME?. If the return range is wrong, you still get that error. That is what you want.

Prefer the built-in argument when you are writing a new XLOOKUP. Use IFNA when you are wrapping VLOOKUP, MATCH, or INDEX/MATCH, or when you are editing a lookup that already exists and you do not want to rewrite it as XLOOKUP.

Excel vs. Google Sheets

Both functions behaved as documented in this Sheets test: IFNA reacted only to #N/A; IFERROR reacted to #N/A, #REF!, #NAME?, #DIV/0!, #VALUE!, #NUM! and #REF! from INDEX. Microsoft’s documentation describes the same split in Excel.

The one Excel-version caveat is older files: IFNA is not in Excel 2010 or earlier. If you must support Excel 2010, you are stuck with IFERROR or with a longer IF(ISNA(...), ...) construction. ISNA tests specifically for #N/A and is the older equivalent of IFNA.

Common mistakes

1. Wrapping a new lookup in IFERROR by habit. It will hide the next typo or a column index that points off the table.
2. Assuming FIND or SEARCH return #N/A when there is no match. In this test both returned #VALUE!, which IFNA does not replace.
3. Using either wrapper to paper over approximate-match VLOOKUP. Approximate match returns a value, not an error, so the wrapper never runs.
4. Treating a replaced error as proof the key is missing. Read the raw formula first, then add IFNA.
5. Forgetting that FILTER’s empty result is #N/A. IFNA is appropriate there if an empty list should display a label. Clear space for the spill first, or you will get a spill error instead, which IFNA will not hide in the useful way.

FAQ

Should I use IFERROR or IFNA with XLOOKUP or VLOOKUP?

Use IFNA, or XLOOKUP’s fourth argument. IFERROR will also hide #REF! and #NAME?. In this test, a VLOOKUP column-index #REF! and an XLLOOKUP typo stayed visible with IFNA and were replaced with IFERROR.

Does IFNA catch #VALUE! or #DIV/0!?

No. ="A"+1 and =1/0 stayed #VALUE! and #DIV/0! under IFNA. IFERROR replaced both.

Why did FIND stay #VALUE! under IFNA?

FIND returns #VALUE! when the search text is not found, not #N/A. IFNA only looks at #N/A. Use IFERROR, or test with ISNUMBER(FIND(…)), if you want a blank when a character is missing.

Is IFNA available in Excel 2010?

No. Microsoft’s documentation has IFNA starting in Excel 2013. Excel 2010 can use IFERROR or IF(ISNA(formula), fallback, formula).

Does a successful lookup change if I wrap it?

No. When XLOOKUP returned 12.5, both IFNA and IFERROR also returned 12.5.

Related tutorials

• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide
• XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?
• XLOOKUP Returns #N/A Even Though the Value Exists: 7 Fixes
• VLOOKUP Returning the Wrong Value? The Approximate Match Trap (and 4 Other Causes)

Live-test findings

All wrapper results in the table above come from one Google Sheets file dated November 12, 2026. IFNA replaced only #N/A (missing XLOOKUP and empty FILTER). IFERROR also replaced #REF!, #NAME?, #DIV/0!, #VALUE! and INDEX’s out-of-range errors. A successful lookup of SKU-101 returned 12.5 with or without either wrapper.

Sources

• Microsoft Support: IFNA function: https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-41b6-94ae-7360814b9ca5
• Microsoft Support: IFERROR function: https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611
• Microsoft Support: ISNA function: https://support.microsoft.com/en-us/office/isna-function-aa27e0e2-d4a7-4c26-abd7-0f0122a921b8
• Google Docs Editors Help: IFNA, IFERROR: https://support.google.com/docs/answer/9367025, https://support.google.com/docs/answer/3093304