**Excel IFERROR Formula (Table of Contents)**

- Examples of IFERROR Functions
- IFERROR Function with Vlookup Function
- Differences Between Formulas (IFERROR, ISERROR, IFNA)

## What is IFERROR Formula?

IFERROR function in Excel is used to find out and manage errors in formulas and calculations. In other words, IFERROR function detects the error and returns or provides another value that is specified otherwise provides the result of the formula. It finds outs for the following errors:-

1. #NA

2. #Value

3. #Ref

4. #Div/0!

5. #NUM!

6. #NAME!

7. #NULL!

**Syntax:**

**Arguments:**

**Value**(required): It is a mandatory column and this can be a value, formula, expression or cell reference. It is to be checked for errors.**Value_if_error**(required): It is also mandatory. It shows what is to return if an error is found. Here we can keep an empty string(blank cell), text message, numeric value, another formula or calculations, etc.

### Examples of IFERROR Functions

Let us look at some practical examples.

#### IFERROR Function

In the below case, in cell E6 the error is kept blank.

Whereas, in E6 error is displayed as “Error”

**Normal Case:**

After using IFERROR Formula the output is shown below.

Same formula is used in cell D3 to D5.

#### ISERROR Function

After using ISERROR Formula the output is shown below.

Same formula used in cell E3 to E5.

#### #N/A Error

It is known as “Value not available” error. If a value is not available in the table array while using the “VLOOKUP” formula, we get to see this “#N/A” error. In the below example, the “VLOOKUP” function is used to find out the sales of salesmen but “Lookup value”(ID) is not available in the master table, so it is showing “#N/A” error.

Using IFERROR Formula in cell F1.

After using the IFERROR formula the output is shown below.

Same formula used in cell F3.

Using VLOOKUP formula in cell G2.

After using the VLOOKUP formula the output is shown below.

Same formula was used in cell G3.

#### #DIV/O! Error

This error is known as** “**Division Error**”**. When a number is divisible by 0, we get to see this error.

After using the above formula the output is shown below.

Same formula is used in cell C3 to cell C5.

#### #VALUE! Error

It occurs on typing an incorrect data type in the formula. In the below example, an alphabet is used in numerator instead of a numeric value. Therefore, the wrong data type is provided in this case. So, it is reflecting **#**Value Error.

After using the above formula the output is shown below.

#### #REF! Error

This is known as “**Reference Error”**. This error occurs when the reference in the formula does not remain valid. Generally, it takes place at the time of deletion of a row/column or a worksheet that was referred in the formula.

After using the above formula the output is shown below.

Same formula is used in cell C3 to cell C5.

In the below example, on deleting the quantity column, the reference in the formula was no longer available and thus returns “**#ref**!” error.

After deleting the Quantity Column the output is shown below.

#### #NUM! Error

This is known as **“NUMBER” **error. For example, if a number is not properly provided, we will get “**#Num!**” error. Also, when an invalid number argument is provided in the formula, it will return a number error.

Return a Number error.

#### #NAME! Error

This error is also known as “**NAME ERROR**”. It is a result of a misspelled function. In the below example, instead of “sum” we have used “sm” as a result of typo which resulted in “#**NAME!”** error.

After applying the above formula the output is shown below.

#### #NULL! Error

This error is very rare in Excel. It is usually a result of a typo where **space **character is used instead of **comma(,)** or **colon(:)** between the cell reference.

In the below case, in the sum formula instead of “colon” we have used “space” between the cell reference by mistake and hence it returns a null error in the answer cell.

** **** **

The following example will show how to use “**IFERROR**” function along with other functions in Excel to get the correct result of a complex task.

### IFERROR Function with Vlookup Function

When “Vlookup” function shows “#NA” result in case lookup value is not available in “array_table”, “Iferror” function helps in replacing the error with an error message that is specified e.g. ”not found” error message instead of “#NA” as an error.

After using formula the output is shown below.

Same formula is used for cell F2 and the output is shown below.

VLOOKUP Formula applied in cell G2.

After using the above formula the output is shown below.

Same formula was used for cell G3 and the output shown below.

In the above case, the “sales” column without using the “IFERROR” function is showing an error as “#NA” whereas the “sales” column using the “IFERROR” function is displaying an error message as **”not found”.**

### Differences Between Formulas (IFERROR, ISERROR, IFNA)

- “Iferror” and “Iserror” are both functions in Excel that help in identifying any error messages like #Ref!, #DIV/0!, #N/A, #VALUE, #NUM!, #NAME!, #NULL!.
- “Iferror” function is a fairly new formula. It was available from 2007 version of excel whereas “Iserror” function is used along with “IF” statement to find the error.
- “Iferror” function detects the error and returns another value that is specified. Otherwise provides the result of the formula. Prior to the development of “Iferror” function, “IF” statement was created along with “Iserror” function in it to test whether there is an error message. In case of an error message, instruct the excel what to write and if no error message exists then reflect the actual result of the formulas or calculations.
- “IFERROR” function was introduced in 2003 to check the error in formula or calculation whereas “IFNA” was introduced in 2013 to check the formula for errors. Syntax of “IFNA” is similar to that of “IFERROR” “IFNA” function only catches “NA” error whereas “IFERROR” function deals with all kinds of error.

### Conclusion

- If a value is empty, it is considered as an “empty string”(“”) and not an error.
- If “value_if _error” is provided as an “empty string”(“”), the cell will remain blank when an error is detected.
- “Iferror” function helps to detect the error in the formula or calculations and in case of error, returns the result as specified by users.
- The purpose of the IFERROR Formula is to catch and manage or handle the errors.

### Recommended Articles

This is a guide to IFERROR Formula in Excel. Here we discuss How to use IFERROR Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

16 Online Courses | 23 Hands-on Projects | 140+ Hours | Verifiable Certificate of Completion

4.8

View Course

Related Courses