In financial modeling and analysis, Excel spreadsheets are filled with numerous data and complex formulas. Building financial models can be complicated and often times Excel formulas don’t necessarily perform calculations as it should be and errors are encountered.

However, Excel provides users with functions that capture and handle specific or all errors. In this article, we will discuss one of these error handling functions, the ISERROR function in Excel. We will discuss the formula and process of ISERROR in Excel. We will illustrate how to use the Excel ISERROR formula together with IF function and HLOOKUP and VLOOKUP Functions. We will also touch on the difference between the IFERROR and the IF(ISERROR) combination.

What is the Error in Excel’s ISERROR

The ISERROR function in Excel is found under the Information functions group. It is designed to test and identify whether an Excel formula’s calculation results in an error. The Excel ISERROR function works with any error type and returns either TRUE/FALSE.

Excel’s ISERROR formula:

Where:

Value – A value, formula, function, or reference cell that is to be tested for any error (required argument).

What error type does it test?

• #N/A
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

If any of the above error is identified then Excel’s ISERROR formula will return TRUE, otherwise FALSE.

As you can see, the Excel ISERROR formula only has one argument. Excel’s ISERROR formula does not give the user the ability to customize the result the ISERROR in Excel, it only displays either TRUE/FALSE.

Here are some basic examples of ISERROR in Excel.

The IF Function and ISERROR Function in Excel Combined

As mentioned, the Excel ISERROR function does not allow users to customize result and the user is limited to TRUE/FALSE. However, combing the IF function with the ISERROR in Excel bypasses this limitation.

Using the same example as before, the IF formula with Excel ISERROR formula =IF(ISERROR(C2),0,C2) returns the number 0%. This replaces the previous result of TRUE.

Let’s go over the process.

Step 1
– Recall the IF function tests whether a condition is met or not and returns one value if TRUE and another value if False.
– The criteria or Logical Test is whether the Excel ISERROR formula, (ISERROR(C2), results in TRUE/FALSE.

Step 2
– If the Excel ISERROR formula identifies an error and returns TRUE, then the IF function’s Value If True argument, the number zero (0), is displayed.

Step 3
– If the Excel ISERROR function identifies no error and returns FALSE, then the IF function’s Value If False argument, the value in cell C2, is displayed.

Step 4
– Since Excel ISERROR formula, (ISERROR(C2), results in #REF! error, then the number 0% is displayed.

Using the same example, let us apply the IF Function to ISERROR in Excel:

Excel ISERROR Function VS IFERROR Function

The either TRUE/FALSE result of the ISERROR function in Excel is a bit of a drawback to most users. For a more versatile or user and financial model-specific result, you have to combine it with the IF function. This is where the alternative (or better function) IFERROR function in Excel comes in. To understand and compare the two functions, let’s continue with the recent example.

=IF(ISERROR(C2),0,C2)

Combines 2 different functions, the IF function and Excel ISERROR function. Essentially, this formula follows a long path or process.

Compare this to the IFERROR function

=IFERROR(C2,0)

Using the IFERROR function, the formula and the process is much shorter as it combines 2 different functions into one single function.

The below table summarizes the key differences between both functions.

Important Notes on Excel ISERROR Function

• ISERROR function in Excel tests if any value, reference cell, or formula returns an error.
• ISERROR in Excel tests all error types and returns TRUE if an error is found otherwise FALSE.
• To return a user-defined and alternative to TRUE/FALSE, combine Excel ISERROR function with the IF Function. On the other hand, use the IFERROR function for a shorter formula and process.
• To catch and handle specific errors, use the following functions:
  • IFNA to catch #NA error only
  • ISERR to catch all errors except #NA error