Read this Microsoft Excel tutorial to learn how to use IFERROR in Excel to replace the symbolic Excel error codes with a descriptive text message, a blank cell, or a color code.

Microsoft Excel offers more than 350 built-in functions. These function syntaxes contain two or more arguments so Excel can read the instructions properly. Excel shows an error message if you make any mistake in the arguments. By wrapping the formulas in the IFERROR function, you can avoid such error codes.

Throughout the tutorial below, I’ll show how to put the IFERROR Excel formula to use, how to place it with different Excel functions, and the best tips when using it.

## What Is the IFERROR Function in Excel?

The IFERROR function allows you to specify a value or action to be returned if another formula results in an error.

It’s truly useful when working with worksheets that contain formulas that might not always be evaluated successfully.

This is the formula syntax of IFERROR:

`=IFERROR(Value,Value_if_error)`

Here’s an explanation of the function arguments:

**Value:**It could be any reference, expression, or value.**Value_if_error:**You can enter any text, numerical, symbol, or simply keep it blank.

### A List of Error Types to Handle

The Excel function IFERROR handles all of the Excel formula error codes you see. Find below an elaborate list:

**#DIV/0!****#N/A****#NAME?****#REF!****#VALUE!****#NULL!****#SPILL!**

ðŸ“’ **Read More**: 6 Ways to Remove #DIV/0! Errors in Microsoft Excel

### Creating a Basic IFERROR Formula in Excel

Suppose, you’re calculating the unit price of a kilogram of tomato by dividing the cost by the net weight of the produce.

Accidentally, you entered `0`

below the **Quantity (KG)** column. In the **Unit Price** column, you’ll get the `#DIV/0!`

error because you’re trying to divide the value in the **Cost** column by `0`

.

Suppose, you don’t have the quantity value with you yet. In this case, you can create the following IFERROR formula to show a message, like “Data Not Available.”

`=IFERROR(C2/B2, "Data Not Available")`

When you hit `Enter`, you get the above message in the destination cell where you need the unit cost of tomato.

## Get a Blank Cell Instead of Errors

You can generate a blank cell instead of a cryptic Excel error to improve the worksheet or dashboard’s readability and visual aesthetics.

More importantly, IFERROR prevents errors from cascading to other formulas that rely on the original cell’s output, ensuring the accuracy of your calculations.

In the above dataset, I’ve created a dashboard to adjust the retail unit price of tomatoes according to the wholesale price.

The larger formula below the **Inflat Price** column header (`A2`

) refers to another formula below the **Unit Price** column header (`D2)`

.

The above Excel function errors occur when I don’t wrap the formula in `D2`

with an IFERROR function.

However, if I use the following formula in `D2`

, the larger formula still calculates ignoring the empty string.

`=IFERROR(C2/B2,"")`

As you can see above Excel still calculated the long formula even if the value in `D2`

is an empty string.

You can use this error-handling function in a small formula referenced to a long formula in another range, worksheet, or even a workbook. Excel won’t consider the empty value and calculate the main formula without any errors.

When data is available for the partial formula, you can update the sheet, and it’ll reflect in the main formula automatically.

## Ignoring Errors in a SUM Function

Often, you use the **SUM** function in an array to find a result by performing multiple calculations in the same formula.

In the above dataset, I’m calculating the total items. My reference data are from a table of **Items**, **Net Cost**, and **Unit Price** for those items.

Here, I can use the following array **SUM** function. It’ll calculate the total items as long as there are no zeroes in the divisor places.

`=SUM($B$2:$B$8/$C$2:$C$8)`

The above screenshot shows that Excel calculated the **SUM** function without any errors.

However, when I add zeroes in the cells `C5`

and `C6`

, the formula breaks and shows the **#DIV/0!** error.

Now, it could be possible that inputs for certain cells are yet not available, or change from time to time. In that scenario, you would want the **SUM** function to still calculate total items from the available data by ignoring `0`

values.

To achieve this, I wrapped the arguments of the array **SUM** formula in the **IFERROR** function. The final syntax of the formula is as outlined below:

`=SUM(IFERROR($B$2:$B$8/$C$2:$C$8,0))`

`$B$2:$B$8/$C$2:$C$8`

attempts to divide each element in the range `$B$2:$B$8`

by the corresponding element in the range `$C$2:$C$8`

.

The **IFERROR** function checks the result of each division. If the division results in an error (e.g., division by zero), IFERROR returns `0`

instead of the error. If there’s no error, it returns the result of the division.

For example, in `C5`

and `C6`

, there are zeroes, so IFERROR generates `0`

instead of the **#DIV/0!** error.

The **SUM** function then adds up all the results from the **IFERROR** function. This includes the valid division results and the `0`

s returned in place of any errors.

## Creating a Nested IFERROR Function for VLOOKUP

Suppose, you need to nest multiple **VLOOKPs** in a single formula to sequentially search through multiple tables for the lookup value. You’ll need to use an operator, like the ampersand, or wrap the **VLOOKUPs** in a function like **CONCATENATE** or **IF**.

When doing so, there are chances of repeated errors. If one **VLOOKUP** part of the whole formula generates the **#N/A** error, irrespective of the result of the other **VLOOKUP** elements, the output will be the error message.

In this situation, you can wrap your **VLOOKUP** formulas into multiple nested IFERROR formulas. Then, the formula will work as outlined below:

- Excel performs calculations for the first
**VLOOKUP**formula. If it works, it’ll show the output by aborting further calculations. - If that doesn’t work, the IFERROR function will trap the error and push Excel to calculate the next
**VLOOKUP**element. - This will go on until Excel finds the lookup value in any one of the referred tables or cell ranges.
- If the lookup value isn’t available in any of the referred ranges, the custom message of IFERROR will show. For example, you might configure the formula to display “
**Not Allowed**” or “**Not Available**.”

Suppose, in the above dataset, you’d like to create a sequential **VLOOKUP** formula to search through **Table1**, **Table2**, and **Table3** for the lookup value and show an output from **column 3**.

You can try to use the following nested formula in `F3`

:

`=CONCATENATE(VLOOKUP(E3,Table1,3,FALSE),",",VLOOKUP(E3,Table2,3,FALSE),",",VLOOKUP(E3,Table3,3,FALSE))`

When you press `Enter`, you’ll get the **#N/A** error.

It’s because the **2nd** and **3rd** **VLOOKUP** formulas are generating this error code.

So, you must replace the above formula with the following:

`=IFERROR(VLOOKUP(E3,Table1,3,FALSE),IFERROR(VLOOKUP(E3,Table2,3,FALSE),IFERROR(VLOOKUP(E3,Table3,3,FALSE),"Not Allowed")))`

Now, as you hit `Enter`, you get **Fruit** in `F3`

since the first **VLOOKUP** formula calculates successfully and the rest produce the **#N/A** errors. However, the IFERROR function traps and hides those errors successfully.

## INDEX With Out-of-Bounds Handling

Suppose, you’ve created an **INDEX** formula in an Excel dashboard. Now, users can enter a row number or reference to a row number in the designated cell to retrieve a value from the source range or array.

The sample dataset could look like the one shown above.

If a user enters an inappropriate value, Excel will show the **#VALUE!** error.

To overcome this and also inform the user that they aren’t supplying the right input, you can use **IFERROR** with **INDEX**.

You can enter the following modified formula in `B2`

:

`=IFERROR(INDEX(A2:A13,C2),"Needs a value between 1 to 12")`

Hit `Enter` to get the desired output in `B2`

.

If you type **Dog** in `C2`

, Excel will show a custom message as supplied through the underlying formula.

However, if you enter any value between `1`

to `12`

, **Output** will show the text string available in that range.

## Conditional Formatting With IFERROR

Suppose, you’ve used some IFERROR formulas throughout the worksheet. You can use Conditional Formatting to highlight those quickly.

You only need to know the custom message you’ve used for the IFERROR formula you’re looking for.

For example, in the above dataset, you want to highlight the cell containing an **IFERROR** function in the cell range `A1:D2`

.

Select the range and click on the **Conditional Formatting** drop-down in the **Home** tab. Click on the **New Rule** option.

The **Edit Formatting Rule** dialog will open. There, make the following changes as outlined below:

- Select the
**Format only cells that contain**rule. - Click on the
**Cell Value**drop-down menu and choose**Specific Text**. - Enter the
**IFERROR**message, like**Check Formula**in the field as highlighted in the screenshot.

Now, click on the **Format** button and create a cell formatting style using the **Number**, **Fill**, **Font**, and **Border** tabs. Click OK once done.

Click **OK** again on the **Edit Formatting Rule** dialog box to apply the **Conditional Formatting rule** you just created.

You should see that Excel has highlighted the cell containing the text given in the Conditional Formatting rule.

Now, you can highlight all the cells that display a specific text due to the implementation of the **IFERROR** formula.

For a different text, simply duplicate the existing rule from the **Conditional Formatting Rules Manager** dialog box.

Now, edit the duplicate rule and enter the different text you want to highlight. Click **OK** and **Apply** to set up the new rule to highlight a different text.

## IFERROR Function With Excel VBA

You can also add the IFERROR function when creating formulas using **Excel VBA** using the `WorksheetFunction.IfError`

method.

Before you begin, learn how to create a VBA macro by reading the following Excel tutorial. If you already know, start with the VBA script outlined below:

ðŸ“’ **Read More**: How To Use The VBA Code You Find Online

You can now use the following VBA script to create a macro that’ll scan the whole worksheet for formula errors. Then, it’ll help you to replace those erroneous formulas with the **IFERROR** function.

```
Sub UpdateFormulasWithCustomIFERROR()
Dim cell As Range
Dim errorText As String
Dim formula As String
' Loop through all cells with formulas
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
formula = cell.formula
If InStr(formula, "=") > 0 Then
' Prompt user for the error text
errorText = InputBox("Enter the text to display for errors in cell " & cell.Address & ":", "IFERROR Text")
' Replace existing formula with IFERROR
cell.formula = "=IFERROR(" & Mid(formula, 2) & ",""" & errorText & """)"
End If
Next cell
End Sub
```

After you’ve created a macro using the above script, press `Alt` + `F8` to get the **Macro** dialog box.

There, select the **UpdateFormulasWithCustomIFERROR** macro and hit **Run**.

Now, the script will show you the following visual instructions:

- The first prompt will ask you for the
**IFERROR**text you want to show.

- The second input box will ask you for the same for the next erroneous formula.
- The prompts will keep showing up until all formulas showing an error code have been replaced.
- You’ll also see the cell address for the formula you’re updating.

Find above the performance of this script.

## Conclusions

If you’re here, congratulations! Now you know how to use IFERROR in Excel in different scenarios and formulas.

You shouldn’t use this formula error-checking function blindly for all the formulas in your worksheet. Use it in scenarios like the ones mentioned below:

- You’re aware of the formula error and it’ll be fixed when you update the worksheet with new data.
- When creating data-entry or tutorial worksheets for employees or project collaborators.
- You’re presenting a dashboard or report to the general public or a less tech-savvy audience.
- Use it in formulas that work better with error-checking functions, like nested
**VLOOKUPs**.

If the tutorial helped you to learn the IFERROR function in Excel, you can acknowledge it below. Do you have any suggestions or feedback? Add those to your comment too.

## 0 Comments