IFERROR Excel Function

The IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error. This returned value can be a text message, an empty string, a logical value, a number, etc. The errors handled by the function include “#N/A,” “#DIV/0!,” “#NAME?,” and so on.

For example, cells A1, A2, and A3 contain the numbers 24, 35, and 78 respectively. We perform the following tasks in the given sequence:

1. To find the minimum number among the listed ones, we enter the formula “=MN(A1:A3)” in cell A4. It returns the “#NAME?” error because the correct function is MIN.
2. To return a custom message, we enter the formula “=IFERROR(MN(A1:A3),"incorrect function name")” in cell A4. It returns the text “incorrect function name” (without the double quotation marks).

In this way, the IFERROR excel function replaces the error message (#NAME?) with a customized text string (incorrect function name).

In Excel, errors are displayed on account of multiple reasons. The IFERROR function detects and deals with these errors the way the user wants.

The purpose of using the IFERROR function is to return a customized and specified result in case a formula evaluates to an error. However, if no error is detected, the function returns the output of the formula.

The IFERROR function is categorized under the Logical functions of Excel.

The syntax of the IFERROR excel function is shown in the following image:

The function accepts the following arguments:

• Value: This is the value to be checked for errors. It can be a cell reference, formula, value or expression.
• Value_if_error: This is the output returned in case an error is found. It can be a text string, blank cell, number, logical value, and so on.

Both the preceding arguments are mandatory.

Let us go through some examples to understand how the IFERROR function handles the Excel errors. Every example covers one Excel error.

#1–“#N/A” Error

The following table shows the names of some employees segregated in two columns titled “list of emp A” and “list of emp B.” Every name is written as “name” followed by a number. Some names are common to both the columns, while some are present in just one column.

We want to create an excel list that separates the common names (present in both the columns) from those absent in the first column (list of emp A). Use the following functions:

• The VLOOKUP function of Excel must look up values in the first column (list of emp A).
• The IFERROR function of Excel must return the text string “name not in list A” for all “#N/A” errors.

The steps to apply the VLOOKUP and IFERROR functions (with reference to the current example) are listed as follows:

Step 1: Enter the following VLOOKUP formula in cell C2.

“=VLOOKUP(B2,$A:$A,1,0)”

Press the “Enter” key. Drag the formula downwards till the cell C18. The output of the VLOOKUP formula is shown in column C of the following image.

The VLOOKUP formula looks for a name of column B in column A. It returns the “#N/A” error (in column C) for the names that are absent in column A. Such names could not be found in column A as they are present only in column B.

Step 2: Replace the “#N/A” errors (shown in column C of the preceding image) with the text string “name not in list A.” For this, enter the following formula in cell C2.

“=IFERROR(VLOOKUP(B2,$A:$A,1,0),"Name not in list A")”

Press the “Enter” key. Drag the IFERROR formula till cell C18.

For the ease of understanding, we have displayed the formulas of column C in the succeeding image. Some cells of column C are shown in green. These are the ones that had returned the “#N/A” error in the preceding step (step 1).

The formula “VLOOKUP(B2,$A:$A,1,0)” becomes the first argument and the string “name not in list A” becomes the second argument of the IFERROR function.

The IFERROR function checks the first argument for an error. If the VLOOKUP formula evaluates to an error, the second argument of the IFERROR function is returned. It has already been stated that the VLOOKUP formula evaluates to an error if the lookup value is not found in column A.

Step 3: The output of the IFERROR formula appears in column C, as shown in the succeeding image.

The IFERROR function returns the name if the VLOOKUP formula does not evaluate to an error. However, if the VLOOKUP formula does evaluate to an error, the IFERROR function returns the string “name not in list A.”

In this way, all the “#N/A” errors have been replaced by the text string “name not in list A.” This string implies that the particular name is not in list A (column A).

Hence, “name 20,” “name 35,” “name 21,” “name 31,” and “name 32” are present in column B but not in column A. The names common to both the columns (shown without color) have been separated (in column C) from those absent in column A (shown in green).

#2–“#DIV/0!” Error

The following table shows some amounts (in $) in the first column titled “amount.” We want to distribute every amount equally among the corresponding number of people mentioned in the second column named “to divide amongst.”

Use the following formulas or functions:

• The division formula must divide the amounts among the stated number of people.
• The IFERROR function must return the text string “no of person < 1” for all “#DIV/0!” errors.

The steps to apply the division formula and the IFERROR function are listed as follows:

Step 1: Enter the following division formula in cell C2.

“=amount/number of people” or “A2/B2”

Press the “Enter” key. Drag the formula downwards till cell C6. The output of the division formula is shown in column C of the following image.

Cell C4 shows the “#DIV/0!” error because the number of people (in cell B4), i.e., the denominator is zero.

Note: In Excel, any number divided by zero returns the “#DIV/0!” error.

Step 2: To replace the “#DIV/0!” error with the text string “no of person < 1,” enter the following formula in cell C2.

“=IFERROR((A2/B2),"No of Person < 1")”

Press the “Enter” key. Drag the formula till cell C6. The output appears in column C, as shown in the following image.

In cases where the argument “A2/B2” does not evaluate to an error, the IFERROR formula returns the amount (in column C). Wherever the first argument (A2/B2) evaluates to an error, the text string “no of person < 1” is returned. This text string (in cell C4) implies that the number of persons among which $6,000 is to be divided is less than 1.

Hence, the amounts listed in column A have been divided among the number of people stated in column B. The amount received by each person is stated in column C.

#3–“#NAME?” Error

The following list shows some numbers to be added. Use the following functions:

• The SUM function in Excel must sum the given numbers.
• The IFERROR function of Excel must return the text string “typed wrong formula” for all “#NAME?” errors.

The steps to apply the SUM and the IFERROR function are listed as follows:

Step 1: Enter the following formula in cell C2.

“=SU(A2:A9)”

The same is shown in the succeeding image.

Press the “Enter” key. The “#NAME?” error appears in cell C2. This is because we have mistakenly entered the function name as “SU” instead of “SUM.”

Step 2: To replace the “#NAME?” error with the text string “typed wrong formula,” enter the following formula in cell C2.

“=IFERROR(SU(A2:A9),"Typed Wrong Formula")”

Press the “Enter” key. The formula is shown in the following image.

Step 3: The output appears in cell C2 of the following image. Hence, the IFERROR function has returned the text string “typed wrong formula” in cell C2. This string implies that the name of the function (SUM) has been typed incorrectly.

The given text string is returned because there is an error in the first argument of the IFERROR function. Had we entered the correct function name (SUM), the output of the IFERROR formula (in cell C2) would have been the sum of the listed numbers, i.e., 180.

#4–“#NULL!” Error

The following list shows three numbers in the range A2:A4. We want to sum the numbers using the following formulas or functions:

• The arithmetic operator “plus” (+) must be used to add the numbers in cells A2, A3, and A4.
• The IFERROR function must return the total of the listed numbers.

The steps to use the plus sign (+) and the IFERROR function are listed as follows:

Step 1: Enter the following formula in cell A5.

“=A2+A3 A4”

Press the “Enter” key. The “#NULL!” error appears in cell A5, as shown in the following image. This error appears because we have forgotten to enter the relevant arithmetic operator before the reference A4. Rather, we have mistakenly entered a space before A4.

Step 2: To replace the “#NULL!” error with the total of the listed numbers in column A, enter the following formula in cell A5.

“=IFERROR((A2+A3 A4),(SUM(A2:A4)))”

Press the “Enter” key.

The first argument, “A2+A3 A4,” is checked for an error. The second argument, “SUM(A2:A4),” is the “value_if_error.” This implies that if the first argument evaluates to an error, the function returns the output of the second argument “SUM(A2:A4).”

Hence, the IFERROR formula returns the sum 600 in cell A5, as shown in the following image.

#5–“#NUM!” Error

The following list shows some numbers. We want to find the square roots of these numbers. Use the following functions:

• The SQRT function in Excel must calculate the square roots of the given numbers.
• The IFERROR function must return the text string “a negative number” for all “#NUM!” errors.

The steps to apply the SQRT Function and the IFERROR functions are listed as follows:

Step 1: Enter the following SQRT formula in cell B2.

“=SQRT(A2)”

Press the “Enter” key. Drag the formula downwards till cell B8. The output of the SQRT function is shown in column B of the following image.

The SQRT function helps calculate the square root of a number. However, the square root of a negative number does not exist. So, the SQRT function returns “#NUM!” errors for such numbers.

Step 2: To replace the “#NUM!” errors with the text string “a negative number,” enter the following formula in cell B2.

“=IFERROR(SQRT(A2),"A Negative Number")”

Press the “Enter” key. Drag the formula till cell B8. The output of the formula is shown in column B of the following image.

The IFERROR function returns the result of the SQRT formula for all positive numbers. For the negative numbers, the IFERROR function returns the text “a negative number.” This text string notifies the user that the number whose square root needs to be found is negative.

Hence, the square roots of the listed numbers have been calculated and the resulting “#NUM!” errors have been replaced with a custom message (a negative number).

#6–“#REF!” Error

The following image shows two numbers in cells A2 and A3. These numbers have been divided with the help of the formula (=A2/A3) entered in cell C2. We want to delete row 3. Use the following techniques and functions:

• The row 3 must be deleted by right-clicking its header and choosing “delete.”
• The IFERROR function of Excel must return the text string “reference deleted” for the “#REF!” errors.

The steps to delete row 3 and apply the IFERROR function are listed as follows:

Step 1: Select row 3 by clicking the header (3) appearing on the leftmost side of the Excel sheet. Right-click the selection and choose “delete” from the context menu.

The row 3 is deleted and the “#REF!” error appears in cell C2, as shown in the following image.

The cell A3 was a part of the formula entered in cell C2. Since Excel is unable to locate the value (or cell reference) of the denominator, it returns the “#REF!” error in cell C2.

Step 2: To replace the “#REF!” error with the string “reference deleted,” enter the following formula in cell C2.

“=IFERROR((A2/#REF!),"Reference Deleted")”

Press the “Enter” key. The output of the IFERROR formula appears in cell C2 of the following image.

The IFERROR function checks whether the first argument (A2/#REF!) evaluates to an error or not. Since it does return an error, the function returns the second argument (reference deleted) as the output.

The text string “reference deleted” implies that the cell reference entered in the division formula has been deleted. This text string is the argument “value_if_error.”

Hence, row 3 has been deleted and the resulting “#REF!” error has been replaced by the string “reference deleted.”

#7–“#VALUE!” Error

The following image shows two values in cells A2 and A3. We want to add these values. Use the following formulas or functions:

• The arithmetic operator plus (+) must sum up the given values.
• The IFERROR function must return the text string “text can’t add to number” for the “#VALUE!” errors.

The steps to use the plus symbol (+) and the IFERROR function are listed as follows:

Step 1: Enter the following formula in cell A5.

“=A2+A3”

Press the “Enter” key. The “#VALUE!” error appears in cell A5. This is because cell A2 contains a number while A3 contains a text string. The addition of a numeric and text value has resulted in an error. The same is shown in the following image.

Step 2: To replace the “#VALUE!” error with the text string “text can’t add to number,” enter the following formula in cell A5.

“=IFERROR((A2+A3),"Text Can’t Add to Number")”

Press the “Enter” key. The output of the IFERROR formula is shown in cell A5 of the following image.

The IFERROR function checks the first argument (A2+A3) for an error. Since this argument does evaluate to an error, the function returns the second argument (text can’t add to number). The text string “text can’t add to number” implies that a text value cannot be added to a numeric value.

Had there been a number in place of the string “bag,” the IFERROR function would have returned the sum of the two numbers.

Hence, since the given values could not be added, the resulting “#VALUE!” error has been replaced by a custom message (text can’t add to number).

Likewise, the IFERROR function can be used for managing errors in Excel. Moreover, the output to be returned (in case of an error) can be customized as per the requirements of the user.