Excel ISERROR Function: A Guide to Mastering Error Detection

Examples of the Excel ISERROR Function

Here are few examples demonstrating the usage of the ISERROR function in Excel:

Example 1: Basic Usage

To make a simple ISERROR formula, provide a reference to the cell you wish to examine for errors. If an error is present, the result will be TRUE; if the tested cell is error-free, the outcome will be FALSE:

Example 2: Combining with IF Function

You can use the Excel ISERROR function in conjunction with the IF function to display a custom message when an error is detected. For example: =IF(ISERROR(A1), “Error in cell”, “No error”)

Example 3: Combining with IF Function & VLOOKUP Function

Suppose you have all student names in column A, and column D contains the list of students who have obtained passing marks in a recent test, as shown in the image below:

You want to figure out which students in the whole class passed the test. To automate this process, you decide to use the formula =IF(ISERROR(VLOOKUP(A2,$D$2:$E$8,2,FALSE)),”No”,”Yes”) in cell B2.

Let’s break down the formula:

• VLOOKUP(A2,$D$2:$E$8,2,FALSE): This part of the formula uses the VLOOKUP function to search for the value in cell A2 within the range $D$2:$E$8. It looks in the second column of this range (column E) and retrieves the corresponding value. If the student’s name is found, it returns the marks obtained; otherwise, it returns an error.
• ISERROR(VLOOKUP(A2,$D$2:$E$8,2,FALSE)): The ISERROR function checks if there is an error in the result of the VLOOKUP function. If there is an error (meaning the student’s name is not found in the list), ISERROR returns TRUE; otherwise, it returns FALSE.
• IF(ISERROR(VLOOKUP(A2,$D$2:$E$8,2,FALSE)),”No”,”Yes”): The IF function then evaluates the result of the ISERROR function. If ISERROR returns TRUE (indicating an error, meaning the student’s name is not found), the formula returns “No”. If ISERROR returns FALSE (indicating no error, meaning the student’s name is found), the formula returns “Yes”.

In simpler terms, the formula checks if the student’s name in cell A2 is present in the list of qualified students. If the name is found, it returns “Yes”; otherwise, it returns “No”.

Difference between ISERROR and IFERROR in Excel

Both ISERROR and IFERROR are functions in Excel that help deal with error. Here is what sets them apart:

• ISERROR checks if a value is an error or not.
• IFERROR, however, is designed to hide errors. When there is an error, it gives you a different value that you choose.

At first, IFERROR might seem like a quicker way to do what IF ISERROR does. But if you look closely, there is a key difference:

• IFERROR only lets you pick a value if there is an error. If there is no error, it just shows the result of the tested value or formula.
• On the other hand, ISERROR is more flexible. It lets you handle both situations – what to do if there is an error and what to do if there is no error.