Template

This tutorial demonstrates how to use the Excel IFERROR Function to catch formula errors, replacing them with another formula, blank value, 0, or a custom message.

IFERROR Function Overview

The IFERROR Function Checks if a formula results in an error. If FALSE, return the original result of the formula. If TRUE, return another specified value.

IFERROR Syntax

To use the IFERROR Excel Worksheet Function, select a cell and type:

=IFERROR(

Notice how the IFERROR formula inputs appear:

IFERROR Function Syntax and Inputs:

=IFERROR(VALUE,value_if_error)

value – An expression. Example: 4/A1

value_if_error – Value or Calculation to perform if the previous input results in an error. Example 0 or “” (blank)

What Is the IFERROR Function?

The IFERROR function falls under the category of Logical Functions in Microsoft Excel, which includes ISNA, ISERROR and ISERR. All of these functions help detect and handle formula errors.

IFERROR allows you to perform a calculation. If the calculation does not result in an error, then the calculation result is displayed. If the calculation does result in an error then another calculation is performed (or a static value like 0, blank, or some text is outputted).

When would you use the IFERROR Function?

• When dividing numbers to avoid errors caused by dividing by 0
• When performing lookups to prevent errors if the value isn’t found.
• When you want to perform another calculation if the first results in an error (ex. Lookup a value in a 2^nd table if it’s not found in the first table)

Un-handled formula errors can cause errors within your workbook, but visible errors also make your spreadsheet less visibly appealing.

If Error Then 0

Let’s look at a basic example. Below you are dividing two numbers.  If you attempt to divide by zero you will receive an error:

Instead, insert the calculation within the IFERROR function and if you divide by zero a 0 is outputted instead of an error:

=IFERROR(A2/B2,0)

If Error Then Blank

Instead of setting errors to 0, you can set them to ‘blank’ with double quotations (“”):

=IFERROR(A2/B2,"")

We will look at more IFERROR usages with the VLOOKUP function…

IFERROR with VLOOKUP

Lookup functions like VLOOKUP will generate errors if the lookup value is not found. As shown above, you can use the IFERROR Function to replace errors with blanks (“”) or 0s:

If Error Then Do Something Else

The IFERROR Function can also be used to perform a 2nd calculation if the 1st calculation results in an error:

=IFERROR(VLOOKUP(A2,LookupTable1!$A$2:$B$4,2,FALSE),
VLOOKUP(A2,LookupTable2!$A$2:$B$4,2,FALSE))

Here if the data is not found in ‘LookupTable1’ a VLOOKUP is performed on ‘LookupTable2’ instead.

More IFERROR Formula Examples

Nested IFERROR – VLOOKUP Multiple Sheets

You can nest an IFERROR inside another IFERROR to perform 3 separate calculations. Here we will use two IFERRORs to perform VLOOKUPs on 3 separate worksheets:

=IFERROR(VLOOKUP(A2,LookupTable1!$A$2:$B$4,2,FALSE),
IFERROR(VLOOKUP(A2,LookupTable2!$A$2:$B$4,2,FALSE),
VLOOKUP(A2,LookupTable3!$A$2:$B$4,2,FALSE)))

Index / Match & XLOOKUP

Of course, IFERROR will also work with Index / Match and XLOOKUP formulas as well.

IFERROR XLOOKUP

XLOOKUP function is an advanced version of VLOOKUP function.

IFERROR INDEX / MATCH

You can also lookup values by using INDEX and MATCH functions in Excel.

IFERROR in Arrays

Array formulas in Excel are used to perform several calculations through a single formula. Let’s suppose there are three columns of Year, Sales, and Avg Price. You can find out the total quantity with the following formula in the E column.

{=SUM($B$2:$B$4/$C$2:$C$4)}

The formula performs well until the divisor range gets an empty cell or zeroes. As a result, you get to see the #DIV/0!error again.

This time, you can use the IFERROR function like this:

{=SUM(IFERROR($B$2:$B$4/$C$2:$C$4,0))}

Notice that the IFERROR function must be nested inside the SUM Function, otherwise the IFERROR will apply to the sum total and not each individual item in the array.

IFNA vs. IFERROR

The IFNA Function works exactly the same as the IFERROR Function except the IFNA function will only catch #N/A errors. This is extremely useful when working with lookup functions: regular formula errors will still be detected, but no error will appear if the lookup value is not found.

=IFNA(VLOOKUP(A2,LookupTable1!$A$2:$B$4,2,FALSE),"Not Found")

If ISERROR

If you are still using Microsoft Excel 2003 or an older version, then you can substitute IFERROR with a combination of IF and ISERROR. Here is a brief example:

=IF(ISERROR(A2/B2),0,A2/B2)

AutoMacro | Excel Automation Made Easy | Free Trial

IFERROR in Google Sheets

The IFERROR Function works exactly the same in Google Sheets as in Excel:

IFERROR Examples in VBA

VBA does not have a built-in IFERROR Fucntion, but you can also access the Excel IFERROR Function from within VBA:

Dim n as long
n = Application.WorksheetFunction.IfError(Value, value_if_error)

Application.WorksheetFunction gives you access to many (not all) Excel functions in VBA.

Typically IFERROR is used when reading values from cells.  If a cell contains an error, VBA may throw an error message when attempting to process the cell value.  Try this out with the example code below (where cell B2 contains an error):

Sub IFERROR_VBA()

Dim n As Long, m As Long

'IFERROR
n = Application.WorksheetFunction.IfError(Range("b2").Value, 0)

'No IFERROR
m = Range("b2").Value

End Sub

The code assigns cell B2 to a variable. The second variable assignment throws an error because the cell value is #N/A, but the first works fine because of the IFERROR function.

You can also use VBA to create a formula containing the IFERROR Function:

Range("C2").FormulaR1C1 = "=IFERROR(RC[-2]/RC[-1],0)"

Error handling in VBA is much different than in Excel. Typically, to handle errors in VBA, you will use VBA Error Handling. VBA Error Handling looks like this:

Sub TestWS()
    MsgBox DoesWSExist("test")
End Sub
 
Function DoesWSExist(wsName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = Sheets(wsName)
    
    'If Error WS Does not exist
    If Err.Number <> 0 Then
        DoesWSExist = False
    Else
        DoesWSExist = True
    End If
 
    On Error GoTo -1
End Function

Notice we use If Err.Number <> 0 Then to identify if an error has occurred.  This is a typical way to catch errors in VBA. However, the IFERROR Function has some uses when interacting with Excel cells.

IFERROR Practice Exercises + Examples

Double-click within a cell to view it’s formula and edit.

 

 

to do:

remove spreadsheet examples at bottom for now…

to test / think about:

• image vs gif at top
• call attention to interactive examples in lesson?
• re-label / re-style code blocks…
• TOC at top? remove them from these pages and manually add [TOC]?
  • put the “excel, googlesheets” VBA links on top and maybe get rid of TOC?  “IFERROR Function available in…”
• what about some other images… like an Excel icon or google sheets or VBA to make it look nicer
  • ya i think see draft email where excel logo gets used and add that someplace…. and make it a fancy header… same with g sheets and vba!
• make font size smaller on site!
• fix CSS… TOC, Syntax area… etc.!

add a download button to download the spreadsheet + ask for email AFTER the download…

or do something like the template producers do… where they ask you to complete a survey