Learn how to use IFERROR & IFNA in Excel with simple explanations, real-life examples, and step-by-step formulas. Perfect for beginners who want to handle Excel errors like #DIV/0!, #N/A, and more easily and professionally.
When you work with Excel, you’re bound to come across scary-looking error messages like #DIV/0!, #N/A, or #VALUE!. While these errors are useful for spotting issues in your formulas, they can make your spreadsheet look messy and confuse users who don’t understand them.
Thankfully, Excel gives us two powerful functions to handle such situations gracefully: IFERROR and IFNA.
Let’s understand how they work with easy-to-follow explanations and real-life use cases.
What Are IFERROR and IFNA?
IFERROR Function
The IFERROR function checks if a formula results in an error. If it does, you can tell Excel what to display instead of the error.
Syntax:
=IFERROR(value, value_if_error)
value: The formula or expression to check.value_if_error: What to return if an error occurs.
Example:
Imagine you’re dividing numbers, but sometimes the divisor is zero:
=A1/B1
If B1 is zero, this will return #DIV/0!.
To handle this with IFERROR:
=IFERROR(A1/B1, "Error: Division by Zero")
Now, instead of an error, the formula will display a friendly message.
IFNA Function
IFNA is similar to IFERROR, but it only catches the #N/A error. This is especially useful with lookup functions like VLOOKUP, HLOOKUP, or XLOOKUP.
Syntax:
=IFNA(value, value_if_na)
value: The expression to evaluate.value_if_na: The result to return if the expression results in#N/A.
Example:
You’re searching for a product name using VLOOKUP:
=VLOOKUP("Tablet", A2:B10, 2, FALSE)
If “Tablet” is not found, it will return #N/A.
To fix that:
=IFNA(VLOOKUP("Tablet", A2:B10, 2, FALSE), "Product Not Found")
Now, if the product is missing, the message is clearer.
Why Use IFERROR or IFNA?
Cleaner Spreadsheets
Error messages can be ugly. These functions let you replace them with blank cells, dashes, or custom messages.
Better User Experience
If you share Excel files with others, friendly messages like “No data” are easier to understand than #N/A.
Avoid Breaking Dashboards
Dashboards or charts pulling from error cells can break. Handling errors prevents this.
IFERROR vs. IFNA: What’s the Difference?
| Feature | IFERROR | IFNA |
|---|---|---|
| Handles all errors | Yes (#DIV/0!, #N/A, etc.) | No, only #N/A |
| Best for | General error handling | Lookup-specific errors |
| Introduced in Excel | Excel 2007 | Excel 2013 |
When to use which?
- Use
IFERRORwhen any kind of error might occur. - Use
IFNAwhen you’re specifically dealing with missing lookup values and want to avoid masking other critical errors.
Real-Life Examples
1. Sales Report Division
Suppose you’re calculating average revenue per sale:
=TotalRevenue/NumberOfSales
What if no sales were made (i.e., NumberOfSales = 0)?
Use:
=IFERROR(TotalRevenue/NumberOfSales, "No Sales Yet")
2. Product Lookup
You’re fetching prices from a list using VLOOKUP:
=VLOOKUP("Laptop", A2:B100,2,0)
If “Laptop” isn’t found:
=IFNA(VLOOKUP("Laptop", A2:B100, 2, 0), "Item not found")
Tips for Beginners
- Use
""(empty quotes) to return a blank cell instead of a message.=IFERROR(A1/B1, "") - You can also return a default number if an error occurs:
=IFERROR(A1/B1, 0) - Combine with other formulas:
=IFERROR(VLOOKUP(A1, DataRange, 2, 0), "Not Found")
Final Thoughts
Using IFERROR and IFNA in Excel is like putting a safety net under your formulas. They help make your sheets look clean, professional, and user-friendly.
Start using them today, especially if you share your Excel work with others, build reports, or analyze data from various sources.
Quick Summary
- IFERROR handles any error.
- IFNA handles only
#N/Aerrors. - Use them to replace errors with friendly messages or default values.
- Makes your spreadsheet cleaner, safer, and easier to use.
Want to learn more about Excel formulas and tricks like this?
👉 Follow this blog for beginner-friendly Excel guides every week!
What’s Next?
In the next post, we’ll learn about the Vlookup in Excel