How to Use The IFERROR Function in Excel – 7 Examples

hen we want to avoid any errors, we typically use the IFERROR function. In Excel, a number of errors, including #N/A, #VALUE, #NUM, #REF, #NAME, etc., are possible. The IFERROR function can be used by assigning a return if errors are present and returning the actual results if errors do not occur.

Purpose of The IFERROR Function

Finding and managing errors.

Syntax of The IFERROR Function

=IFERROR(value, value_if_error)

Arguments of The IFERROR Function

value(required): The argument (reference, value, formula) to detect an error.

value_if_error(required): return value if the reference cell contains an error.

7 Examples of IFERROR Function in Excel

Example #1: Use The IFERROR Function For #N/A Error

When we vlookup a value that isn’t present in the chosen range, an #N/A error happens. We wanted to find the obtained marks of some students from the left-side table (B4:C10). In order to handle the #N/A error, we used the IFERROR function. It shows “Name Not Available” if the cell contains a #N/A error.

=IFERROR(VLOOKUP(E4,$B$4:$C$10,2,0),"Name Not Available")

Explanation:

=VLOOKUP(E4,$B$4:$C$10,2,0)      //Return: #N/A

=IFERROR(VLOOKUP(E4,$B$4:$C$10,2,0),"Name Not Available")   //Return: Name Not Available 

Example #2: Use The IFERROR Function For #VALUE! Error

A #VALUE error occurs when the cell’s values are mismatched type. For example, if you sum numerical values with text values, it shows a #VALUE error. You can use the IFERROR function to test whether a cell carries a #VALUE error.

=IFERROR(C4+C5+C6+C7+C8+C9, "Failed")

Explanation

• In range C4:C9, C4 to C8 cells are numerical values but cell C9 is a text value.

Example #3: Use The IFERROR Function for #REF! Error

An #REF error occurs when we put the wrong reference in a formula. For the following VLOOKUP formula cell F4 shows a #REF error. 

=VLOOKUP(E4,$B$4:$C$10,3,0)

• Then, we will apply the IFERROR function to check whether the cells contain any errors.

=IFERROR(F4, "check reference column number")

Explanation

• =VLOOKUP(E4,$B$4:$C$10,3,0), in this formula’s col_ind_num argument we type 3 which refers to column number 3. But, in our range, we have only 2 columns. So, it shows a #REF error.
• =IFERROR(F4, “check reference column number”) returns check reference column number as the cell F4 contains an error and we referred it to the value_if_error argument. 

Example #4: Use The IFERROR Function for #DIV/0! Error

The #DIV error occurs when there is no value or zero in the denominator position. In column E, we will use the IFERROR function to test whether the results of column B divided by column C contain any error.

=IFERROR(B4/C4,"No Value in Denominator")

Explanation

• =IFERROR(B4/C4,”No Value in Denominator”) in this formula, as in cell C4 the denominator value is zero, so the IFERROR function finds a #DIV error and returns No Value in Denominator.

Example #5: Use IFERROR Function for #NUM! Error

The #NUM error occurs when an invalid numerical value is entered into a formula. We will use the IFERROR function to examine the test to see whether it contains the #NUM error.

=IFERROR(POWER(B7,1/2), "Complex Number")

Explanation

• As cell B7 contains a negative number -225, the square root of this number can’t be possible in real numbers. So, IFERROR(POWER(B7,1/2), “Complex Number”) finds a #NUM error.

Example #6: Use the IFERROR Function for #NAME! Error

The #NAME error occurs in cells containing any unrecognized function name or range, any syntax error, or any text value that is not enclosed in the double quotation marks. In the image below, cell F5 shows a #NAME error. 

=VLOKUP(E5,$B$4:$C$10,2,0)

Explanation

• =VLOKUP(E5,$B$4:$C$10,2,0) the formula contains an function name error. The function name should be VLOOKUP.
• =IFERROR(F5, “Unrecognized Function Name or Range, Syntax Error”), so, this formula finds an #NAME error.

Example #7: Use IFERROR Function for #NULL! Error

The #NULL error occurs when using space instead of a comma or colon while writing a formula or a range. The following formula will show a #NULL error.

=SUM(C4 C9)

Explanation:

• =IFERROR(E4, “Space is used instead of comma or colon”) returns the value for an error as the formula in cell E4 should be =SUM(C4:C9), but we have used space in the cell range (C4 C5)

Frequently Asked Questions

Can I use the IFERROR function with multiple formulas?

Yes, you can use the IFERROR function with multiple formulas by nesting the functions. For example, you could use the following formula:

=IFERROR(IF(Formula1, Formula2), Value)

What is the difference between the IFERROR and IFNA functions?

The IFERROR function catches all errors, whereas the IFNA function specifically catches #N/A errors. If you only want to catch #N/A errors, use the IFNA function instead of the IFERROR function.

Conclusion

In the above discussion, we have shown you how to use the IFERROR function for about seven possible errors in Excel. We hope you will be able to understand the errors and how to avoid that with the help of the IFERROR function. Please, leave comments if you have any queries.