Pro Templates

Excel IFERROR function

Summary

The Excel IFERROR function is designed to detect errors within a formula and return a specified value if an error is found. It helps manage and trap errors like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! in a more streamlined manner compared to nested IF statements. This function is particularly useful for maintaining the readability and cleanliness of data.
Syntax
				
					=IFERROR(value, value_if_error)
  • value: The value, reference, or formula to check for an error.
  • value_if_error: The value to return if an error is found.
Return value
Returns the specified value for error conditions or the result of the formula if no error is detected.

How to use

To use the IFERROR function, input the formula or reference you wish to check for errors as the first argument. Specify the value to return in case an error is detected as the second argument. The function will return the result of the formula if it’s error-free, or the specified value if an error is found.

Examples

IFERROR for Division
When dividing two numbers where the divisor might be zero:
				
					=IFERROR(A2/B2, "Cannot divide by zero")
This formula will return the result of A2 divided by B2 if B2 is not zero. If B2 is zero, it will return “Cannot divide by zero” instead of the default #DIV/0! error.
IFERROR and VLOOKUP
To handle errors from a VLOOKUP function:
				
					=IFERROR(VLOOKUP(E2, A2:B10, 2, FALSE), "Item not found")
This formula tries to find the value in E2 within the range A2:B10. If found, it returns the corresponding value from the second column. If not found or if an error occurs, it returns “Item not found”.

Using IFERROR vs IFNA

When handling errors in Excel, IFERROR and IFNA both offer solutions, but they serve different purposes. IFERROR is a broad solution that catches any type of error within a formula, making it a great choice for formulas where multiple error types could occur, and a unified response is sufficient. It streamlines error handling by providing a single fallback for all errors, but this convenience may come at the cost of masking various types of errors, some of which might require special attention. 

On the other hand, IFNA is specifically designed to catch only #N/A errors, which commonly occur in lookup functions when a value isn’t found. It’s ideal when you’re confident that #N/A is the only error your formula could encounter and other errors, if they occur, need to be visible for diagnosis. IFNA ensures that you’re informed about other potential issues in your data or formula by not masking errors other than #N/A. 

In choosing between IFERROR and IFNA, consider the nature of the errors your formula might encounter and the balance you need to strike between convenience and precision. IFERROR offers a one-size-fits-all solution, while IFNA allows for more targeted error handling while keeping other types of errors in plain sight.

Additional Notes

  • IFERROR can trap all types of errors, which in some cases might mask unexpected problems in your data or formula. Use it when you’re certain of the types of errors you expect.
  • For situations where only #N/A errors need to be caught, consider using the more specific IFNA function.
  • Always ensure that the value_if_error is appropriate for the possible errors and the context of your data to avoid misleading results.
  • In Excel 2013 and later versions, you can use additional error-handling functions like ISERROR, ISERR, ISNA, and ERROR.TYPE for more customized error handling strategies.

Related Functions

Excel ISNA function

The Excel ISNA function tests for the #N/A error value, returning TRUE if found, important for handling missing data errors.

Excel ISERROR function

The Excel ISERROR function checks for any error value, returning TRUE if any is detected, essential for comprehensive error checking.

Content Navigation