Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 14

Error Handling with IFERROR and IFNA

Recommended resources

Welcome to our next lesson. In our previous session, we explored how to use approximate match lookups to classify data into tiers, and we saw how XLOOKUP offers a more robust solution than VLOOKUP. You also saw that XLOOKUP has a built-in argument to handle cases where a value isn't found. However, most Excel functions don't have this feature, and formulas can often result in error messages like #N/A, #DIV/0!, or #VALUE!.

These errors not only look unprofessional in a finished report but can also break other calculations that depend on them. For a freelancer building tools for clients, presenting clean, error-free results is paramount. This lesson focuses on mastering two essential functions, IFERROR and IFNA, to handle these errors gracefully. Our goal is to ensure your spreadsheets are both robust and user-friendly.

The Universal Error Handler: IFERROR

The most common way to handle errors in Excel is with the IFERROR function. It acts as a safety net, checking if a formula results in an error and, if it does, allowing you to specify a custom output instead.

The syntax is straightforward:
=IFERROR(value, value_if_error)

  • value: The formula or cell reference you want to evaluate.
  • value_if_error: What you want to display if the value argument results in any error. This can be text (in quotes), a number, another formula, or even an empty string ("") to show a blank cell.

Errors can be particularly disruptive because they can cascade through your worksheet. A single error in one cell can cause errors in many other cells that reference it. Let's watch a video that demonstrates this problem and how IFERROR solves it.

The Excel IFERROR Function: Clean up Your Excel Errors

The "Technology for Teachers and Students" channel provides an excellent, practical demonstration of IFERROR. It shows how an error originates, how it impacts other formulas, and three different ways to resolve it.

Please watch the video from the beginning. Pay attention to these key moments: The initial problem at the start: Notice how the #DIV/0! errors, caused by dividing by zero, prevent the AVERAGE function at the bottom from working correctly. The first solution in this segment: See how the original division formula is "wrapped" in an IFERROR function to replace the error with a 0. This immediately fixes the broken AVERAGE calculation. An alternative solution shown here: Observe how you can use custom text, like "hasn't started yet", as the output instead of a number. This can make your sheet much more informative for the end-user.

As you saw, IFERROR is a powerful and versatile tool. It catches all of the following common Excel errors: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!.

Handling Lookup Errors with Precision: IFNA

IFERROR is great, but its "catch-all" nature can sometimes be a disadvantage. Imagine a complex formula where several different types of errors could occur. IFERROR would treat them all the same, potentially hiding the true source of a problem.

This is especially relevant for lookup functions like VLOOKUP or XLOOKUP, where the most common and expected error is #N/A (meaning "Not Available" or "Not Found"). In these cases, you might want to handle only the #N/A error and still be alerted to other unexpected errors like #REF! (an invalid cell reference) or #VALUE! (a wrong data type).

For this, we use the IFNA function. It works just like IFERROR, but it is specialized to only catch the #N/A error.

The syntax is nearly identical:
=IFNA(value, value_if_na)

Most Common Excel Errors #️⃣ and How To Fix Them - Avoid Broken Formulas

In this clip, Leila Gharani demonstrates a perfect use case for IFNA.

Please watch the section from the video. The presenter explains that while you can use IFERROR for lookup errors, the IFNA function is designed specifically for this purpose, making your formula's intent clearer.

IFERROR vs. IFNA: Choosing the Right Tool

So, when should you use IFERROR and when is IFNA the better choice? This is a key decision that separates good spreadsheets from great ones. Using the more precise tool makes your work easier to debug and more reliable.

Consider the following scenario:

In this example, the IFERROR column masks all issues, showing "error" for both the division by zero (`#DIV/0!`) and the wrong data type (`#VALUE!`). The IFNA column, however, correctly ignores these errors, letting you see the actual problem. It would only act if a `#N/A` error appeared.

Here is the fundamental principle for choosing between them:

  • Use IFNA for lookup formulas (VLOOKUP, HLOOKUP, MATCH, XLOOKUP). The #N/A error is an expected outcome when an item isn't found. Using IFNA allows you to provide a user-friendly message like "Not Found" while still being alerted to unexpected formula errors.
  • Use IFERROR for mathematical calculations (like division) or other formulas where various error types are possible and you want a single, simple way to handle them all.

The following reading provides a great summary and a handy decision table to help you choose.

Excel IFERROR/IFNA Functions

This article from ZetCode gives a concise comparison of the two functions and provides clear guidance on when to use each.

First, review the introduction and syntax table for a quick recap. Then, jump to the final section, "Choosing Between IFERROR and IFNA", and study the decision table. It provides clear scenarios and reasons for picking one function over the other.

By choosing the correct error-handling function, you make your formulas more robust and your intentions clearer—a critical skill for building professional-grade tools for clients.

Conclusion

In this lesson, you've learned how to move beyond letting Excel display jarring error messages. By trapping errors and replacing them with meaningful feedback, you significantly improve the quality and professionalism of your work.

Here are the key takeaways:

  • Unhandled formula errors look unprofessional and can break dependent calculations.
  • IFERROR is a general-purpose function that catches any type of error and replaces it with a value you define.
  • IFNA is a specialized function that catches only the #N/A error.
  • As a best practice, use IFNA for lookup functions to handle "not found" results, and use IFERROR for general calculations where multiple error types might occur.

In our next lesson, we'll continue our theme of making spreadsheets more professional and maintainable. We will explore Named Ranges, a feature that allows you to replace cryptic cell references like C2:C53 with intuitive names like SalesData, making your formulas dramatically easier to read, write, and manage.