IFERROR Function

In my previous article, I discussed about the different types of errors we get in Microsoft excel while working with numbers, text etc. These errors we get in Excel maybe it is #N/A or #VALUE! or #REF! (or #DIV/0! or #NUM! or #NAME? or #NULL and so on) is very irritating while analyzing data because it will not allow you to perform basic operations like SUM, SUMIF or SUBTOTAL etc. (it will show errors).

Therefore, Excel has given us one function named IFERROR to overcome such situations. 

Today I will discuss about IFERROR function with few examples.

The IFERROR function is a built-in function in Excel that is categorized as a Logical Function. 

Syntax

The syntax for the IFERROR function in Microsoft Excel is:

=IFERROR(value, value_if_true)

Parameters or Arguments

value

The ‘value’ may be a formula or simply division or anything which has an outcome.

value_if_true 

The alternate value that is returned if the formula results in an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). Otherwise, the function will return the result of the formula if no error occurs.

If you remember the functionality of IF function, then it will be very easy to understand. ‘value_if_true’ is the outcome you want to display or printed IF THERE IS ANY ERROR in the ‘value’ parameter.

Let us have two examples and I hope the functionality of this function will be very clear.

Examples :

In the below image, I want to show you a very small calculation on Target vs Achievement in percentage, it is Achievement divided by Target. But you know that whenever I want to divide some value with a Zero it will return an undefined value as output (which is the basic rule in mathematics) and in Excel it is division error (#DIV/0!).

How to use IFERROR Function in Excel

Similarly, whenever I try to divide some numeric value by some Text, it will also report me an error in Excel as #VALUE!.
Now, if I use IFERROR Function the problem will disappear. What I do in my daily data analysis for very long formula is that I first type the formula and then I move at the beginning of the formula after ‘=’ sign, type IFERROR and one opening bracket and then I move to the end of the formula and type a comma and usually I put a ‘0’.

In place of zero you can use anything you wish, like ‘OUTPUT-ERROR’ etc.

Note : If you are comfortable using VLOOKUP by typing at the formula bar or within the Cell, you can start typing like “=IFERROR(VLOOKUP(…….),0)”

In the below example, I will show you how you can use IFERROR function in VLOOKUP.

How to use IFERROR Function with VLOOKUP Function in Excel

There are two tables, one is TABLE – 1 which is the data provider, and another is TABLE – 2, data to be mapped here from TABLE – 1.

If you check carefully at TABLE – 1, you will notice there are four names A, B, C & D whereas in the TABLE -2 name field contains names as A, X, P & D.

In this situation whenever I try to map TABLE - 2 using VLOOKUP the X & P will display #N/A errors because of unavailability of the data. But if I use IFERROR function before the VLOOKUP function it will replace the #N/A by the value I need (here I opted for “VALUE NOT FOUND”)

Hope this article is enough for you to understand the IFERROR function. If any question arises, feel free to comment in the comment box below.

Thanks for reading…