Excel troubleshooting

Why Is IFERROR Not Working?

Fix IFERROR formulas that still show errors, hide the wrong value, or fail because of argument order and nested error chains.

Problem

  • IFERROR usually fails when the error happens outside the wrapped expression, when IFERROR itself is misspelled, or when a different error appears downstream.

Quick fix

  • Wrap the exact expression that produces the error, use IFNA for #N/A only, and fix the source formula before hiding the error message.

Common IFERROR mistakes

  • Only part of the formula is wrapped, so another argument still returns an error.
  • IFERROR hides a real data problem that should be fixed first.
  • The replacement value is text when the formula result must stay numeric.

Better alternatives

  • Use IFNA when you only want to catch #N/A from lookup functions.
  • Use IFERROR after confirming the lookup or reference logic is correct.
  • Combine IFERROR with data cleanup functions like TRIM and VALUE.

Frequently asked questions

  • IFERROR vs IFNA for lookups? IFNA only catches #N/A — better for VLOOKUP/XLOOKUP. IFERROR catches every error type.
  • Why do I still see #REF! with IFERROR? The error may be outside the wrapped expression. Wrap the full formula or fix the broken reference.
  • Does IFERROR hide data problems? Yes — fix lookup logic first, then add IFERROR for user-friendly display.

Extended diagnostic workflow

  • Problem: IFERROR usually fails when the error happens outside the wrapped expression, when IFERROR itself is misspelled, or when a different error appears downstream.
  • Quick fix: Wrap the exact expression that produces the error, use IFNA for #N/A only, and fix the source formula before hiding the error message.
  • Reproduce on three sample rows before changing production formulas shared with the whole team.
  • Save version history or a copy before bulk find-replace across thousands of cells.
  • If the fix works on a sample but fails in production, compare text vs number storage on key columns.

Related functions to verify

  • IFERROR (/functions/iferror/): =IFERROR(value, value_if_error) — Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.
  • IFNA (/functions/ifna/): =IFNA(value, value_if_na) — Returns a value you specify if a formula returns #N/A; otherwise returns the formula result.
  • ISERROR (/functions/iserror/): =ISERROR(value) — Returns TRUE if the value is any error value.
  • XLOOKUP (/functions/xlookup/): =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) — Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • VLOOKUP (/functions/vlookup/): =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) — Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.

Escalation and documentation

  • Rebuild the formula on a blank sheet with minimal ranges to isolate the failing argument.
  • Document the root cause in a shared runbook so the next teammate does not repeat the same fix.
  • Link to fix-excel-formula-errors (/guides/fix-excel-formula-errors/) when multiple error types appear.
  • Escalate to Power Query or IT when the source system export format changed without notice.