IFERROR in Excel

When applying formulas in an Excel spreadsheet, some error values ​​are bound to be generated. Excel provides a useful function to handle these errors: the IFERROR function. The IFERROR function returns a custom result when a formula evaluates to an error and returns a normal result when no error occurs. Check out how IFERROR works in Excel in our blog, and understand with examples.

What is IFERROR in Excel?

The Excel IFERROR function helps to manage errors that may occur in formulas. It substitutes a value in case it finds an error. Rather than presenting error messages like #DIV/0!, #N/A, or #VALUE!, the IFERROR function allows you to substitute the errors with more user-friendly results—such as text ("Not Available") or a certain numeric value (e.g., 0).

IFERROR allows spreadsheets to appear more presentable and simpler to interpret, particularly while collaborating with other teams or implementing them within dashboards or reports.

The use of IFERROR in Excel is as follows: 

• Identifying and dealing with formula errors
• Replacing typical error messages with the output
• Ensuring the logical continuity of data processing, even where specific cells may be full of errors

IFERROR Syntax

=IFERROR(value, value_if_error)

Arguments

• value: The expression, formula, or cell reference you want to check for errors.
• Value_if_error: The result you want Excel to return if the value argument results in an error.

Summary of IFERROR Function in Excel

If you want to boost your data analytical skills then start with learning MS Excel! We recommend you to select the most suitable MS Excel online courses as per your professional and personal goals. Learn how to use both basic and advanced Excel formulas and functions, dashboards, macros, pivot tables, and data analysis tools to improve business insights and reporting.

Examples: IFERROR Function in Excel 

Listed are some examples of IFERROR Function in Excel to understand how to use this function:

Example 1: Replace Errors with a Blank Cell or Custom Text

Scenario: You are calculating price per unit using the formula Sale ÷ Unit. But when the Unit is 0 or empty, Excel shows an error. Let's see how to do it - 

Step 1: Go to the cell where you want the result.

Step 2: Enter the formula:

=IFERROR(B2/C2, "")

This shows a blank cell if there’s an error (like dividing by 0).

If you prefer to show a message instead of a blank, enter:

=IFERROR(B2/C2, "Error")

This shows the word "Error" if something goes wrong.

Example 2: Use IFERROR with VLOOKUP to Show “Not Found”

Scenario: You use VLOOKUP to find values in a table. But if a match isn’t found, Excel shows #N/A.

Step 1: Select the cell where you want to perform the lookup.

Step 2: Enter this formula:

=IFERROR(VLOOKUP(D2, $A$2:$B$11, 2, FALSE), "Not Found")

This shows "Not Found" when the item doesn’t exist in the list.

Example 3: Use Nested IFERROR with VLOOKUP to Check Multiple Tables

Scenario: You have two different tables. You want Excel to search in the first one, and if the item isn’t found, try the second one.

Step 1:  Click on the target cell.

Step 2: Enter this formula:

=IFERROR(VLOOKUP(G2, $A$2:$B$7, 2, FALSE), IFERROR(VLOOKUP(G2, $D$2:$E$7, 2, FALSE), "Not Found"))

Excel first looks in the first table. If not found, it will look in the second table.

If still not found, it shows "Not Found".

Key Observations

• You can use the IFERROR function to detect and handle errors in an expression.
• If the value of value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
• The IFERROR function is based on the IF function and uses the same error messages, but has fewer arguments. The relationship between the IFERROR function and the IF function is as follows:
• IFERROR(A,B) := IF(ISERROR(A), B, A)

IFERROR checks if expression A results in an error using ISERROR; if it does, it returns B (the specified alternative value); otherwise, it returns A (the original result). This equivalence is how IFERROR simplifies error handling compared to nesting IF and ISERROR functions.

• This feature is not supported in DirectQuery mode when used in computed columns or row-level security (RLS) rules.

Related Reads