Excel IFERROR Formula Examples: When to Use It and When Not To

IFERROR Syntax

=IFERROR(value, value_if_error)

Excel evaluates the first argument. If it returns any error, IFERROR returns the second argument instead. The errors it catches: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

The second argument can be:

• An empty string "" to show a blank cell
• A number like 0
• A text message like "Not found" or "No data"
• Another formula that runs only when an error occurs

Copy-Paste IFERROR Examples

=IFERROR(VLOOKUP(A2,$F$2:$G$100,2,FALSE),"")

=IFERROR(XLOOKUP(A2,Products[SKU],Products[Price]),"SKU not found")

=IFERROR(A2/B2,0)

=IFERROR(TEXT(B2/C2,"0.0%"),"No data")

=IFERROR(INDEX(ReturnRange,MATCH(A2,LookupRange,0)),"Not found")

=IFERROR(A2*B2,"Check quantity or price")

=IFERROR((B2-A2)/A2,"N/A")

=IFERROR(A2&" - "&TEXT(B2,"$#,##0"),"")

IFERROR vs IFNA: Which One to Use

This is the most important distinction. IFERROR catches every error type. IFNA catches only #N/A.

For lookup formulas where #N/A is expected (when a value simply is not in the table), IFNA is the safer choice:

=IFNA(XLOOKUP(A2,SKU,Price),"Not found")

With IFNA, a #REF! error caused by a deleted column, or a #VALUE! error caused by mismatched data types, still shows as an error instead of being silently hidden. This is how you keep genuine problems visible while still handling expected lookup misses cleanly.

Use IFERROR only when you genuinely want to suppress all error types — for example, in a concatenation formula where many different errors are all equally harmless.

When IFERROR Is Appropriate

IFERROR is the right choice when an error is expected, understood, and harmless. The most common valid use cases:

• Lookup formulas in reports: Not every ID in a report will have a matching record. A #N/A blank is cleaner than the error text for stakeholders who just need to read the output.
• Division in percentage columns: Division by zero is expected when a category has no baseline. IFERROR(..., 0) or IFERROR(..., "N/A") prevents noise in summary rows.
• Dashboard KPIs: Executive dashboards should show clean values or labeled blanks, not formula errors. IFERROR keeps the presentation professional.
• Data entry templates: Formulas in input templates often reference cells that start blank. IFERROR prevents errors from showing before a user has filled in required fields.

When IFERROR Is Dangerous

IFERROR becomes a liability when it hides errors that represent real problems. The most common dangerous patterns:

• Wrapping every formula in IFERROR(..., 0): If a formula breaks because a referenced sheet was deleted or a column was renamed, the cell silently shows 0 instead of alerting you. Totals built on those zeros look correct but are wrong.
• Masking #REF! errors: A #REF! means a formula references a range that no longer exists. This should be investigated and fixed, not hidden.
• Hiding #NAME? errors: A #NAME? means Excel cannot recognize a function name — often because of a typo or a missing add-in. Hiding it creates the illusion that the formula works when it does not.
• Using IFERROR(..., "") in sum ranges: Blank cells look identical to zero in many views, but downstream SUMIF and AVERAGE formulas may treat them differently.

The Right Workflow: Find Errors First, Then Decide

Before wrapping any formula in IFERROR, understand why the error occurs:

1. Let the error show without IFERROR.
2. Identify the error type (#N/A, #REF!, #DIV/0!, etc.).
3. Determine whether the error is expected (a normal missing-lookup result) or a real problem (a deleted reference, a broken formula).
4. If expected: wrap with IFERROR or IFNA and choose the appropriate fallback value.
5. If a real problem: fix the underlying formula or data issue first.

The Spreadsheet Auditor can identify all formula errors across every sheet in a workbook, making step 1 and 2 fast for large files.

Nesting IFERROR for Fallback Lookups

IFERROR can run a second formula when the first one fails. This is useful when you want to try multiple lookup tables in sequence:

=IFERROR(
  VLOOKUP(A2,PrimaryTable,2,FALSE),
  IFERROR(
    VLOOKUP(A2,SecondaryTable,2,FALSE),
    "Not in any table"
  )
)

This looks up in the primary table first. If not found, it tries the secondary table. If still not found, it returns a message. This is a valid use of nested IFERROR because each level represents a genuinely expected failure.

IFERROR With Array Formulas

When using IFERROR inside an array formula or with dynamic array functions in Excel 365, the function applies to the entire spilled array:

=IFERROR(XLOOKUP(A2:A100,SKU,Price),"Not found")

This returns a spilled column of prices, replacing any #N/A results with "Not found" individually for each row. The IFERROR applies element-by-element across the entire array result.

Common IFERROR Mistakes

• Using 0 as the fallback for lookup results: If a price of 0 is a valid value in your data, using IFERROR(VLOOKUP(...),"0") makes it impossible to distinguish a genuine zero price from a missing record.
• Hiding errors in audit-sensitive workbooks: Financial models, compliance reports, and audited spreadsheets should expose errors, not hide them. Auditors look for IFERROR wrapping as a flag.
• Confusing IFERROR and IF: IFERROR is only for errors. IF(A2="","No value",A2) is the right function for handling blank cells — IFERROR does not catch blank cells, only formula errors.

Frequently Asked Questions

Does IFERROR slow down Excel?

IFERROR itself has negligible performance impact. However, formulas that must evaluate fully before IFERROR can check the result may still be slow. A VLOOKUP that scans a 100,000-row range still runs the scan — IFERROR only takes over after the VLOOKUP finishes or errors. For performance, address the underlying formula efficiency rather than relying on IFERROR to shortcut the calculation.

Can I use IFERROR to return another formula?

Yes. The second argument of IFERROR can be any valid formula. A common pattern is trying a faster lookup first and falling back to a slower but more flexible one if the first fails:

=IFERROR(XLOOKUP(A2,Table[ID],Table[Name]),VLOOKUP(A2,LegacyRange,2,FALSE))

How do I find all cells using IFERROR in my workbook?

Go to Home > Find & Select > Find, type IFERROR in the search box, and make sure to search within Formulas. This shows every cell where IFERROR is used. Review them to confirm each one is hiding an expected, harmless error rather than a real problem.