Introduction
When working in Excel, errors like #N/A, #DIV/0!, or #VALUE! often clutter your reports. Instead of confusing end-users, you can use the Excel IFERROR function to replace errors with cleaner outputs such as 0, "Not Found", or even a blank cell.
In this guide, you’ll learn:
- The syntax of IFERROR
- 20 real-world IFERROR problems with solutions
- How to download a free IFERROR Excel practice file
What is the Excel IFERROR Function?
The IFERROR function in Excel is used to trap and handle errors in formulas. It prevents your spreadsheets from displaying messy error codes and ensures your reports look professional.
IFERROR Function Syntax
=IFERROR(value, value_if_error)- value → Formula to evaluate.
- value_if_error → What to show if an error occurs.
Example:
=IFERROR(VLOOKUP(A2, D2:E10, 2, FALSE), "Not Found")
If VLOOKUP fails, it shows Not Found instead of #N/A.
Example Dataset (Practice File)
| Employee | Sales | Target | Bonus | Division |
|---|---|---|---|---|
| John | 500 | 600 | #N/A | North |
| Mary | 800 | 700 | 100 | South |
| Sam | #DIV/0! | 500 | #N/A | East |
| Rita | 1000 | 1200 | #N/A | West |
| Alex | 700 | 700 | 200 | North |
20 IFERROR Problems & Solutions
1. Replace VLOOKUP #N/A with “Not Found”
=IFERROR(VLOOKUP(A2,D2:E10,2,FALSE),"Not Found")
2. Show 0 instead of #DIV/0!
=IFERROR(A2/B2,0)
3. Display “Missing Data” for blank lookups
=IFERROR(VLOOKUP(C2,F2:H10,2,FALSE),"Missing Data")
4. Replace #N/A with employee’s Division
=IFERROR(VLOOKUP(A2,H2:J10,2,FALSE),E2)
5. Show “Check Input” if formula fails
=IFERROR(SQRT(D2),"Check Input")
6. Return blank instead of error
=IFERROR(A2/B2,"")
7. Replace #VALUE! with 0 in calculations
=IFERROR(D2*E2,0)
8. Nested IF – Show Bonus or 0
=IFERROR(IF(D2>E2,100,0),0)
9. Handle missing text lookups
=IFERROR(INDEX(F2:F10,MATCH(A2,E2:E10,0)),"Not Available")
10. Replace errors with average sales
=IFERROR(A2/B2,AVERAGE(A:A))
11. Show “Invalid Data” if text in numbers
=IFERROR(VALUE(C2),"Invalid Data")
12. Replace NA with last known valid value
=IFERROR(C2,D1)
13. Handle division safely
=IFERROR(A2/B2,"Safe Divide")
14. Lookup & divide safely
=IFERROR(VLOOKUP(A2,D2:E10,2,FALSE)/C2,0)
15. Replace #REF! with message
=IFERROR(D2+E2,"Cell Deleted")
16. Convert text to number safely
=IFERROR(VALUE(A2),0)
17. Fix errors in % Growth calculation
=IFERROR((B2-C2)/C2,"N/A")
18. Replace errors with median value
=IFERROR(A2/B2,MEDIAN(A:A))
19. Handle lookup across multiple sheets
=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found in Sheet2")
20. Clean up CONCAT errors
=IFERROR(A2&" - "&B2,"Missing Info")Why Use the IFERROR Practice File?
- 20 problems from beginner to advanced
- Covers finance, HR, reporting & data cleaning
- Real-world formulas with clean results
- Free Excel practice file – Download the IFERROR practice Excel file
