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 0s 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.