Syntax
IFERROR returns a custom result if a formula throws an error, otherwise it returns the formula’s normal result.
=IFERROR(value, value_if_error)Quick Start (Copy & Use)
Handle Divide by Zero
=IFERROR(A2/B2, "")| A (Value) | B (Divisor) | Result |
|---|---|---|
| 100 | 10 | 10 ✅ |
| 50 | 0 | (Blank) |
| 75 | 5 | 15 |
| 40 | 0 | (Blank) |
VLOOKUP / XLOOKUP with IFERROR
VLOOKUP Safe Search
=IFERROR(VLOOKUP(E2, A:B, 2, FALSE), "Not found")| ID | Item |
|---|---|
| 101 | Apple |
| 102 | Banana |
| 103 | Orange |
| 104 | Mango |
| Lookup Value | Result |
|---|---|
| 101 | Apple |
| 104 | Mango |
| 105 | Not found |
| 999 | Not found |
XLOOKUP with Default
=XLOOKUP(E2, A:A, B:B, "Not found")Note: XLOOKUP has a built-in “not found” argument, so IFERROR is optional here.
| ID | Item |
|---|---|
| 101 | Apple |
| 102 | Banana |
| 103 | Orange |
| 104 | Mango |
| Lookup Value | Output |
|---|---|
| 102 | Banana |
| 104 | Mango |
| 200 | Not found |
| 500 | Not found |
Text Cleanup
Convert Text to Number
=IFERROR(VALUE(A2), "Invalid number")| Input | Result |
|---|---|
| 123 | 123 |
| "45" | 45 |
| abc | Invalid number |
| (Blank) | Invalid number |
Date Handling
Days Between Two Dates
=IFERROR(DATEDIF(A2,B2,"d"),"Invalid date")| Start Date | End Date | Result |
|---|---|---|
| 01-Jan-2025 | 05-Jan-2025 | 4 |
| 10-Feb-2025 | 15-Feb-2025 | 5 |
| 01-Mar-2025 | (Blank) | Invalid date |
| Invalid | 05-Mar-2025 | Invalid date |
AVERAGE with IFERROR
Safely Calculate Average
=IFERROR(AVERAGE(A2:A5), "No valid data")| Values | Result |
|---|---|
| 10 | 15 |
| 20 | |
| 15 | |
| #DIV/0! |
Avoid Divide by Zero
Percentage Calculation
=IFERROR(Sales/Total, "")| Sales | Total | Result |
|---|---|---|
| 500 | 1000 | 50% |
| 200 | 400 | 50% |
| 300 | 0 | (Blank) |
| 100 | 0 | (Blank) |
Combine IF and IFERROR
Wrap IFERROR around IF logic for safer dashboards.
=IFERROR(IF(A2>70,"Pass","Fail"),"Check input")| Score | Result |
|---|---|
| 85 | Pass |
| 40 | Fail |
| #N/A | Check input |
| (Blank) | Check input |
Best Practices & Gotchas
- Use
IFERRORto hide user-facing errors. - Don’t overuse – errors may indicate real data issues.
- Combine with
IFfor conditional error handling. IFNAis a lighter alternative if you only want to catch#N/A.- For text and date formulas, IFERROR helps prevent “Invalid” showing in reports.
FAQ
What errors does IFERROR catch?
It catches #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.
Is IFERROR available in all Excel versions?
Yes, since Excel 2007. For earlier versions, use IF(ISERROR(...),...,...).
How is IFERROR different from IFNA?
IFNA only handles #N/A, while IFERROR handles all errors.
Can I return a blank instead of an error?
Yes. Use =IFERROR(A1/B1,"").
Does IFERROR work in Google Sheets?
Yes, it works exactly the same in Sheets.
Can I use IFERROR with text or dates?
Yes. Use IFERROR(VALUE(A1),"Invalid number") for text or IFERROR(DATEDIF(A1,B1,"d"),"Invalid date") for dates.
What if my average or sum has an error?
Wrap your function: =IFERROR(AVERAGE(A1:A10),"No data").
Need a ready workbook?
Download the full Excel file with all
Download the full Excel file with all
IFERROR examples, answer keys, and a cheatsheet.