IFERROR Function in Excel – Remove Excel Error

It is commonly said that “No matter how expert you are in excel, you would definitely encounter with excel formula error 😛 while working with formulas in excel. Handling excel errors is something that every excel user must know. In this blog, we would unlock one of the most important excel formulas that helps to handle formula errors in excel called the ‘IFERROR function in Excel’.

There are different types of errors that an excel user might encounter while working with formulas. This includes #N/A, #VALUE!, #NAME?, #DIV/0!, #REF!, #NULL!, #NUM!, and ######. Each of these errors occurs due to some specific reason and can be resolved.

Before going through this blog, I would recommend you to check my tutorial on 8 common excel formula errors and how to resolve them.

Now, when you are aware of the common excel errors, let us unlock the IFERROR excel function which is an excellent way to handle different types of errors in excel.

What is the Use of IFERROR Excel Formula

The =IFERROR formula in excel is a special excel function which is used to specific return a value if any of the excel formula gives an error.

In simple terms, if any excel formula or function does not give an error, then excel would return the result of that formula. However, in case the formula gives an error, then the IFERROR formula would come into the picture and return a specified value instead of that error.

Syntax and Arguments

=IFERROR(value, value_if_error)

The above formula syntax has two basic arguments in it.

• value – In this argument, enter the entire formula which you want to check for the error.

• value_if_error – In this argument, specify the value that you want to return in the cell if the value argument (above) gives any of these errors – #N/A, #VALUE!, #NAME?, #DIV/0!, #REF!, #NULL!, or #NUM! error.

Examples of IFERROR Function in Excel

To being with, let us take one very basic example to understand how the syntax and arguments of the IFERROR formula work in Excel.

As you know, any number divide by 0 gives #DIV/0! excel error. Logically, this is correct behavior. Mathematically, you cannot divide a number by 0.

But what if you do not want excel to give an error code, instead it should return the text – ‘Incorrect Operation’.

Simply embed the formula 4/0 inside the IFERROR function and enter the text “Incorrect Operation” as its second argument:

=IFERROR(4/0,”Incorrect Operation”)

As a result, you would notice that instead of error code, excel returned the specified value.

Similarly, you can even return blank or empty cell instead of formula error code in excel. Or you can let excel return zero (0) instead of formula error code.

To convert formula error code to a blank or empty cell, simply use open and close double-quotes. Similarly, to convert formula error to zero, type 0 within double-quotes. See the image below:

Using IFNA Function Instead of IFERROR Formula

Another excel function that is capable of handling formula errors is the IFNA function in Excel. The IFNA formula also allows an excel user to return a specified text/value if a formula calculation results in an error.

The only difference between IFNA vs the IFERROR formula is that the former only handles the #N/A error code, while the IFERROR function handles all the types of excel formula errors.

The VLOOKUP function is a commonly used lookup formula in excel to return a corresponding value. The VLOOKUP formula gives a #N/A error in case it can’t find the corresponding lookup value. In order to return some specific text/value like “Not Found” or “Not Available” when VLOOKUP function can not find value, use the IFNA formula:

To return “Not Found” instead of #N/A error in VLOOKUP, use IFNA formula in Excel.

=IFNA(VLOOKUP(E1,$A$2:$B$5,2,0),”Not Found”)

As a result, the above formula would return the text “Not Found” if VLOOKUP function returns #N/A formula error.

Thank You 🙂