IFERROR Excel Function

What is IFERROR Function in Excel?

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.

Syntax

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

IFERROR Function Formula

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.

How to Use IFERROR Excel?

Let us go through some examples to understand how the IFERROR function handles the Excel errorsErrors In ExcelErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more. Every example covers one Excel error.

You can download this IFERROR Function Excel Template here – IFERROR Function Excel Template

#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 listCreate An Excel ListA list can be created in Excel to define a list of items/values as predefined values. It may be created using the Data Validation tool so that users may select from a list rather than entering their own values.read more 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.
IFERROR Function Example

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.

IFERROR Function Example 1

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.

IFERROR Function Example 3

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.
IFERROR Function Example 4

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.

IFERROR Function Example 5

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.

IFERROR Function Example 6

#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.
IFERROR Function Example 7

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.”

IFERROR Function Example 8

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.

IFERROR Function Example 9

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 [SU(A2:A9)] 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.

IFERROR Function Example 10

#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.

Handling #NULL! Error Example 4-1

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.

Handling #NULL! Error Example 4-2

#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.
Handling #NUM Error Example 5

The steps to apply the SQRT FunctionSQRT In ExcelThe Square Root function is an arithmetic function built into Excel that is used to determine the square root of a given number. To use this function, type the term =SQRT and hit the tab key, which will bring up the SQRT function. Moreover, this function accepts a single argument.read more 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.

IFERROR Function Example 5-1

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).

IFERROR Function Example 16

#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.
Handling #REF! Error Example 6

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.

Handling #REF! Error Example 6-1

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 referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more 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.”

Handling #REF! Error Example 6-2

#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.

Handling #VALUE! Error Example 7

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).

Handling #VALUE! Error Example 7-1

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.

Frequently Asked Questions

1. Define the IFERROR function of Excel.

The IFERROR function detects and handles the various types of Excel errors like “#N/A,” “#DIV/0!,” “#REF!,” “#VALUE!,” and so on. In place of these errors, the function returns a text message, number, empty string, logical value, etc.

The syntax of the IFERROR function of Excel is stated as follows:

“IFERROR(value,value_if_error)”

The first argument “value” is checked for errors. The second argument, “value_if_error,” is returned in case an error is found in the first argument. Both the arguments of the IFERROR function are mandatory.

2. When is the IFERROR function used and how does it work in Excel?

The IFERROR function is used in the following situations:

a. To capture the error and notify its presence to the user
b. To tell Excel what is to be done in case the first argument runs into an error
c. To replace the error with a customized message

The IFERROR function works as follows:

a. The function checks the first argument for errors.
b. In case the first argument does evaluate to an error, the function returns a predefined response. This response is entered as the second argument in the function.
c. In case the first argument does not run into an error, the function returns the result of the first argument.

3. How does the IFERROR function return a blank cell in Excel?

To return a blank cell, an empty string needs to be entered in the second argument of the IFERROR function. For example, two cells contain the following values:

• Cell A1 contains 20
• Cell A2 contains 0

Enter the formula “=IFERROR(A1/A2,””) in cell B1. After pressing the “Enter” key, it returns an empty cell (B1).

As long as cell A2 contains zero, the given IFERROR formula returns a blank cell. This is because the formula identifies division by zero as an error. The moment a value is entered in cell A2, the IFERROR formula returns the result of A1/A2.

Had A1 been a blank cell and A2 contained 20, the formula “=IFERROR(A1/A2,“no message”) would have returned 0. This is because the formula does not identify a blank cell in the numerator as an error. Hence, it returns the result of the first argument.

Recommended Articles

This has been a guide to IFERROR Excel function. We discuss how to handle excel errors (#DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!,) with examples. You may also look at these useful functions of Excel

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>