IFERROR vs ISERROR: Which One Should You Use?

The short answer to IFERROR vs ISERROR: IFERROR replaces an error with a value you choose, while ISERROR only tells you whether something is an error by returning TRUE or FALSE. Use IFERROR to clean up a formula's output, and ISERROR when you need a yes-or-no test, such as counting or highlighting errors. This guide is for anyone tidying reports or answering error-handling questions in an Excel test.

IFERROR vs ISERROR at a glance#

IFERROR gives you a finished result; ISERROR gives you a test that you build on.

"All of them" means #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. If you only want to catch #N/A from a lookup, neither is ideal; see IFERROR vs IFNA.

How the syntax differs#

IFERROR takes two arguments: the formula to try, and what to show if it fails.

=IFERROR(value, value_if_error)

ISERROR takes one argument and answers a question: is this an error?

=ISERROR(value)

Because ISERROR only says TRUE or FALSE, you normally wrap it in IF to decide what to show. Before IFERROR arrived in Excel 2007, this was the standard pattern:

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

It works, but it has two weaknesses. You type the formula twice, so a later edit to one copy and not the other creates a silent bug. And Excel calculates it twice, which slows down large sheets with heavy lookups. The IF function guide explains the IF part if it is new to you.

Updating IF(ISERROR()) in older files#

If you inherit a workbook built before Excel 2007, you will often see the long pattern. You can shorten it safely when the value tested and the value returned are the same formula:

Check that both halves really match before you change anything. If the test formula and the returned formula differ, as in the MATCH example later in this article, the original logic is doing something different and IFERROR is not a direct swap. Leave those as IF(ISERROR()).

The same table handled both ways#

The difference is clearest on one set of data. Column D divides Sales by Orders to get the average order value, with =B2/C2 filled down:

Ben has no orders, so dividing by 0 gives #DIV/0!. Eli's sales cell holds text, so the maths gives #VALUE!.

With IFERROR: replace the errors#

Change the formula in D2 to this and fill it down:

=IFERROR(B2/C2,"")

Ana still shows 150, but Ben and Eli now show an empty-looking cell. A total or average below the column keeps working, because AVERAGE skips text, including the empty text "". =AVERAGE(D2:D7) returns 150, the average of the four real values.

Be careful with a fallback of 0. With =IFERROR(B2/C2,0), the average becomes 600 ÷ 6 = 100, because the two zeros count as real values. Pick the fallback that keeps your later maths honest.

With ISERROR: find and count the errors#

Keep the plain =B2/C2 formulas and add a check column. In E2:

=ISERROR(D2)

This shows TRUE for Ben and Eli and FALSE for everyone else. To count the problem rows in one cell:

=SUMPRODUCT(--ISERROR(D2:D7))

The result is 2. The double minus (--) turns TRUE into 1 and FALSE into 0 so SUMPRODUCT can add them. In Microsoft 365 or Excel 2021, =SUM(--ISERROR(D2:D7)) works too.

Here you don't hide the errors: you measure them. That is often the better first step, because each error points to a data problem that someone should fix.

When to use IFERROR#

Use IFERROR when the error is expected and you know what should show instead. Good examples:

• Division that can hit zero, like averages or rates for new staff: =IFERROR(B2/C2,"").
• Lookups where a missing item is normal: =IFERROR(VLOOKUP(A2,Prices,2,FALSE),"Not listed"). The guide to using IFERROR with VLOOKUP covers this in depth.
• Reports for other people, where an error looks broken and a blank or "n/a" looks finished.
• Formulas feeding totals, so one bad row doesn't turn SUM into an error.

The IFERROR function guide and the full IFERROR tutorial show more examples, including how to return a blank cell instead of 0.

When to use ISERROR#

Use ISERROR when you need TRUE or FALSE rather than the value itself. It is the better tool when:

• You count or audit errors, as with =SUMPRODUCT(--ISERROR(D2:D7)).
• You highlight errors with conditional formatting. Select D2:D7, choose Home > Conditional Formatting > New Rule > Use a formula, and enter =ISERROR(D2).
• You want two different labels, neither of which is the formula's result. For example, to check whether a customer ID in A2 exists in a list:

=IF(ISERROR(MATCH(A2,$F$2:$F$50,0)),"New","Existing")

MATCH returns a position number, which you don't want to show. ISERROR turns "found or not found" into TRUE or FALSE, and IF picks the label.

• You need a test inside AND or OR, such as flagging rows where the price lookup fails and the order is large.

ISERROR is one of a family of IS functions, alongside ISBLANK, ISNUMBER and ISTEXT. The article on Excel IS functions shows them side by side.

ISERROR vs ISERR vs ISNA#

Excel has three error tests, and they differ only in how they treat #N/A:

ISNA is the precise choice for lookups, because #N/A means "not found" while other errors mean something is broken. The list of Excel error types explains what each error means.

Common mistakes with IFERROR and ISERROR#

These slips cause most of the trouble:

1. Hiding errors you should fix. IFERROR also hides #NAME? from a typo and #REF! from a deleted column. Build and test the formula first, then add IFERROR last.
2. Expecting ISERROR to return the value. =ISERROR(B2/C2) shows FALSE for Ana, not 150. Wrap it in IF, or use IFERROR.
3. Typing the formula twice and editing only one copy in =IF(ISERROR(x),0,x). Switch to IFERROR so the formula exists once.
4. Using 0 as the fallback before an average. Zeros pull averages down. Use "" if the cell should be ignored.
5. Quoting a number fallback. =IFERROR(B2/C2,"0") returns the text "0", which SUM ignores. Write 0 without quotes.

Our recommendation#

For everyday work, reach for IFERROR: it is shorter, faster and easier to read than IF(ISERROR()), and it works in every current version of Excel. Use ISERROR when the question is "is this an error?" rather than "what should show instead?": counting errors, conditional formatting, and IF formulas with their own labels.

In short: IFERROR to replace errors, ISERROR to detect them. And for lookups, prefer IFNA so real mistakes still show.

Practise it#

Try the exercise on this page: it counts the error rows with ISERROR. Then practise IFERROR in a live spreadsheet, where you clean up division and lookup errors and the sheet checks each formula instantly. Finish with the IS functions and SWITCH lesson to test what is really in a cell.