Cellwise Try it free

Logic function

Excel IFERROR function

IFERROR returns the result of a formula, or a value you choose if that formula gives any error. Use it to show "Not found", 0, or a blank instead of #N/A or #DIV/0! in reports.

=IFERROR(value, value_if_error)

Arguments

value
The formula or value to check for an error.
value_if_error
What to return if value is an error, like "" or 0.

IFERROR examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1RepCallsDeals
2Nina12018
3Tom00
4Ivy859
5Raj14025
  1. =IFERROR(C2/B2,0)

    Result: 0.15 shows as 15% with Percent format

    Nina made calls, so there is no error and IFERROR returns her close rate: 0.15.

  2. =IFERROR(C3/B3,0)

    Result: 0

    Tom made 0 calls, which would give #DIV/0!. IFERROR shows 0 instead.

  3. =IFERROR(MATCH("Zoe",A2:A5,0),"No such rep")

    Result: No such rep

    Zoe is not on the list, so the friendly message replaces #N/A.

  4. =IFERROR(C4/B4,"")

    Result: 0.10588235294 shows as 10.6% with Percent format

    Returning "" leaves the cell blank when there is an error. Ivy has calls, so her rate shows: about 10.6% once the cell is formatted as Percent.

How to use IFERROR

  1. Write the formula that might fail.
  2. Wrap it: type =IFERROR( before it.
  3. Add a comma and the value to show if it errors.
  4. Close the bracket and copy it down.

Common IFERROR mistakes

Hiding real problems

IFERROR catches every error, including typos that cause #NAME? or #REF!. Use IFNA when you only expect #N/A.

Returning "" and then summing

Empty text is ignored by SUM but breaks + and *. Return 0 if the cell feeds other math.

Wrapping every formula by habit

Errors are signals. Only wrap formulas where an error is expected and harmless.

IFERROR FAQ

How do I replace #N/A with 0 in Excel?

Wrap the formula: =IFERROR(VLOOKUP(A2,D:E,2,FALSE),0). For lookups, =IFNA(...,0) is safer because it leaves other errors visible.

What is the difference between IFERROR and IFNA?

IFERROR catches every error type. IFNA only catches #N/A, so mistakes like #REF! or #NAME? still show up.

How do I use IFERROR with VLOOKUP?

Put the whole VLOOKUP inside: =IFERROR(VLOOKUP(A2,Table,2,FALSE),"Not found"). Missing values then show your message.

Practice IFERROR in these lessons

Guides that use IFERROR

Related functions

Updated