IFERROR Function: Error Handling Guide (2025)
Quick Answer: IFERROR catches errors: =IFERROR(formula, fallback). Example: =IFERROR(A1/B1, "Error") shows "Error" if division fails. Handles all error types: #DIV/0!, #N/A, #VALUE!, #REF!. Excel 2007+.
What is IFERROR?
IFERROR is Excel's error-handling function. It checks if a formula produces an error and returns a custom value instead. Clean spreadsheets without ugly #N/A or #DIV/0! errors.
Simple Example:
=IFERROR(A1/B1, "Cannot divide") // If division works → shows result // If B1 is 0 → shows "Cannot divide" // Catches #DIV/0! error
Without IFERROR:
=A1/B1 → #DIV/0!
With IFERROR:
=IFERROR(A1/B1, 0) → 0
Error Types IFERROR Catches
#DIV/0!
Division by zero
#N/A
Value not available (VLOOKUP)
#VALUE!
Wrong type of value
#REF!
Invalid cell reference
#NUM!
Invalid numeric value
#NAME?
Unrecognized formula name
#NULL!
Invalid range intersection
💡 Common Use Cases
- • VLOOKUP protection: Handle "not found" cases gracefully
- • Division safety: Prevent #DIV/0! in calculations
- • User-facing reports: Clean display without technical errors
- • Data validation: Provide meaningful messages instead of errors
- • External data: Handle missing/broken links
IFERROR Syntax
=IFERROR(value, value_if_error)
1. value (required)
The formula or cell to check for errors.
A1/B1 ← Division formulaVLOOKUP(A1, Table, 2, FALSE) ← Lookup formulaC5 ← Simple cell reference2. value_if_error (required)
What to display if an error occurs.
0 ← Return zero"" ← Return blank"Not Found" ← Custom messageA1 ← Return original valueCommon Patterns
Return Zero on Error
=IFERROR(A1/B1, 0)
Return Blank on Error
=IFERROR(VLOOKUP(A1, Table, 2, 0), "")
Custom Message
=IFERROR(A1/B1, "Cannot calculate")
Alternative Formula
=IFERROR(VLOOKUP(...), INDEX(MATCH(...)))
⚠️ Important Notes
- • Excel 2007+: IFERROR requires Excel 2007 or later (not in Excel 2003)
- • Catches ALL errors: Cannot distinguish between error types (use ISERROR + IF for specific errors)
- • Performance: Formula evaluated only once (more efficient than IF(ISERROR(...)))
- • Don't overuse: Hiding errors can make debugging difficult
- • Nested IFERRORs: Can nest for multiple fallbacks
12+ IFERROR Examples
1. Safe Division
=IFERROR(A2/B2, 0)
Returns 0 if B2 is zero (prevents #DIV/0!)
2. VLOOKUP with Fallback
=IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Found")
Shows "Not Found" instead of #N/A when lookup fails
3. Clean Blank Display
=IFERROR(A2/B2, "")
Returns blank cell instead of error (cleaner reports)
4. Percentage Calculation
=IFERROR(C2/D2*100&"%", "N/A")
Safe percentage with formatting
Multiple Lookups
5. Nested VLOOKUP Fallback
=IFERROR(VLOOKUP(A2,Table1,2,0), VLOOKUP(A2,Table2,2,0))
Try Table1, if error try Table2
6. Multiple IFERROR Layers
=IFERROR(VLOOKUP(...), IFERROR(INDEX(MATCH(...)), "Not Found"))
Chain multiple fallback attempts
Text Formulas
7. Safe Text Extraction
=IFERROR(LEFT(A2, FIND(" ", A2)-1), A2)Extract first name, return full text if no space
8. Array Formula Protection
=IFERROR(AVERAGE(IF(A2:A10>100, A2:A10)), 0)
Handle array formula errors
Advanced Techniques
9. Conditional Error Messages
=IFERROR(A2/B2, IF(B2=0, "Divide by zero", "Other error"))
Different messages for different scenarios
10. Sum with Error Handling
=SUMPRODUCT(IFERROR(A2:A10/B2:B10, 0))
Sum array, treating errors as zero
11. External Reference Safety
=IFERROR('[OtherFile.xlsx]Sheet1'!A1, "File not available")Handle broken external links
12. Date Calculation Safety
=IFERROR(DATEDIF(A2, B2, "d"), "Invalid dates")
Handle invalid date ranges
🎯 Pro Tips
- • Use sparingly: Don't hide errors that indicate real problems
- • Meaningful fallbacks: Use descriptive messages, not just 0 or blank
- • Debug mode: Temporarily remove IFERROR to see actual errors
- • IFNA alternative: Use IFNA for #N/A only (Excel 2013+)
- • Error checking: Leave IFERROR off during development, add for production
Frequently Asked Questions
Should I wrap every formula in IFERROR?
No! Overusing IFERROR hides legitimate errors and makes debugging impossible. Use it only where errors are expected (VLOOKUP, division, external data). During development, leave formulas unwrapped to catch mistakes. Add IFERROR for production/user-facing reports.
What's the difference between IFERROR and IFNA?
IFERROR catches ALL error types (#DIV/0!, #N/A, #VALUE!, etc.). IFNA (Excel 2013+) catches ONLY #N/A errors. Use IFNA for VLOOKUP/XLOOKUP where you want to handle 'not found' but still see other errors like #REF! or #VALUE!.
Why is IFERROR better than IF(ISERROR())?
Efficiency and simplicity. IF(ISERROR(formula), fallback, formula) evaluates the formula TWICE. IFERROR(formula, fallback) evaluates it ONCE. IFERROR is also shorter and more readable. Always prefer IFERROR in Excel 2007+.
Can I use IFERROR with array formulas?
Yes! IFERROR works with array formulas. Example: =IFERROR(A1:A10/B1:B10, 0) returns array with errors replaced by 0. Useful with SUMPRODUCT: =SUMPRODUCT(IFERROR(A1:A10/B1:B10, 0)) safely sums division results.
How do I see what error IFERROR is hiding?
Temporarily remove the IFERROR wrapper. Or use conditional formatting to highlight cells with IFERROR: Find & Select → Go To Special → Formulas → Errors. For debugging, replace IFERROR with the formula alone to see actual errors.
Can I return different values for different errors?
Not directly with IFERROR (it catches all errors equally). For specific error handling, use: =IF(ISERROR.TYPE(A1)=2, 'Div by zero', IF(ISERROR.TYPE(A1)=7, 'Not found', A1)). Or use IFNA for #N/A only, IFERROR for others.