Handle Excel Errors with IFERROR and ISERROR (#N/A, #VALUE!, #DIV/0!)
Clean results for users and accurate diagnostics behind the scenes. Use IFERROR and ISERROR together to achieve both.
Syntax and Differences
| Function | Syntax | Key Point |
|---|---|---|
| IFERROR | =IFERROR(formula, value_if_error) | Returns a replacement value if any error occurs in the formula |
| ISERROR | =ISERROR(value) | Returns TRUE for an error and FALSE otherwise → IF(ISERROR(…),"",value) |
| IFNA | =IFNA(formula, value_if_na) | Replaces only #N/A errors (for lookup failures) |
| ERROR.TYPE | =ERROR.TYPE(value) | Returns an error-type number (for logging or branching) |
Principle: Use IFERROR for display and branch with ISERROR or IFNA in calculation logic to avoid hiding the root cause.
Replace with Messages or Default Values
Return a Blank
=IFERROR(VLOOKUP(F2, Tbl, 3, FALSE), "")Display a Guidance Message
=IFERROR(1/0, "Waiting for input")Default Value (Keep the Previous Value)
=IFERROR(new_value, previous_value)Combine with LOOKUP Functions
Use a Message as XLOOKUP’s Default Value
=XLOOKUP(F2, code, price, "Unregistered code")Hide VLOOKUP/INDEX-MATCH Errors
=IFERROR(INDEX(price, MATCH(F2, code, 0)), "Not found")Look Up Multiple Candidate Keys (OR) — Return the First Success
=LET(v, IFERROR(XLOOKUP({F2,G2,H2}, code, price), NA()),
XLOOKUP(TRUE, NOT(ISNA(v)), v))Protect Part of a Formula (Advanced)
Protecting only the problematic part of a complex calculation makes debugging easier.
Example: Prevent Division by Zero
=IF( denominator=0, 0, numerator/denominator )Example: Neutralize Only a TEXTSPLIT Failure
=LET(parts, IFERROR(TEXTSPLIT(A2,"-"),""), INDEX(parts,1))Debugging and Quality Monitoring
Count Errors
=COUNTIF(range, "#N/A") + COUNTIF(range, "#VALUE!") + COUNTIF(range, "#DIV/0!")Count by Error Type
=SUM(--(ERROR.TYPE(range)=7)) // Example: 7=#DIV/0!User-Friendly Display
=IFNA(XLOOKUP(F2, code, price), "Unregistered (contact: extension 1234)")Common Mistakes and Best Practices
- Overusing IFERROR → It hides the root cause. Use ISERROR or IFNA for branching within calculations.
- Numbers stored as text → A common cause of #VALUE! errors. Use VALUE/NUMBERVALUE or normalize the source data.
- Division by zero → Check the denominator before dividing.
- Chained errors → Errors in earlier steps propagate to later steps. Separate and test each step.
- Missing logs → Record error counts and types on a separate sheet for quality management.
Summary
| Situation | Recommended Approach |
|---|---|
| Lookup failures only | IFNA(formula,"Not found") |
| Hide all errors | IFERROR(formula,"") |
| Zero denominator | IF(den=0,0,num/den) |
| Debugging | Branch or count with ISERROR and ERROR.TYPE |