< Go Back

Excel IFERROR Function

Formula

=IFERROR(value,value_if_error)

Overview of the Excel IFERROR Function

The Excel IFERROR function works by providing a pre-determined result whenever a formula produces an error but gives a standard result whenever there is no error. IFERROR is a smart way to detect and manage errors without using any complex nested IF expressions.

Purpose of the Excel IFERROR Function

Used for detecting and customizing error messages

Return Value for the Excel IFERROR Function

The exact custom value you set where an error occurs.

Logical Arguments for the Excel IFERROR Function 

  • Value: This comprises of the formula, value or reference to check for an error.
  • Value_if_error: This represents the customs value displayed if the error was detected

Usage Notes of the Excel IFERROR Function

To detect and manage errors produced by another formula or function, you can use the IFERROR function. The Excel IFERROR function is designed to check for errors such as: #DIV/0!, #NUM!, #NAME?, #N/A, #VALUE!, #NULL!, or #REF!

Example 1

If B8 contains 23, C8 contains 13, and D8 contains 0 and E8 has this formula

=IF(B8<23,“F”,C8>=13,“A”)

The subsequent formula will automatically catch the #NAME? Error.

=IFERROR(B8<23,“F”,C8>=13,“A”"Wrongvalue")

In this particular scenario, E8 will show the message “Wrong value” instead of #NAME?

Figure 1: Example showing how the Excel IFERROR Function manages error message

The Excel IFERROR function can also be used to detect the #N/A error displayed by VLOOKUP whenever  a lookup value isn’t displayed

=IFERROR(VLOOKUP(value,data,column,0),"Notavailable ")

The Excel IFERROR functions will function a result whenever VLOOKUP shows a result. However, once VLOOKUP displays a #N/A, IFERROR shows “Not available” instead. 

Notes about the Excel IFERROR Function

  • Where the value is  blank, it will be seen as a blank  string (“”) instead of an error
  • Also, If value_if_error is provided as a blank string (“”), in this case, no message will be shown whenever an error is found.
  • If the Excel IFERROR function is entered in the form of a range of formula, it will return a range of results with one item from the array for each cell in value.
  • If you are using Excel 2013 or higher versions, you can detect and handle the #N/A error using specifically the IFNA function
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar