Skip to content

Excel function · Logical

Excel IFERROR function

IFERROR returns the result of a formula; if the formula returns an error such as #DIV/0!, #N/A or #VALUE!, it shows the value you chose instead. It keeps tables clean when dividing by zero or when a lookup finds nothing.

In Turkish Excel: EĞERHATA

What it does

The first argument is the formula to calculate and the second is what to show if it fails. If the formula works, its result comes back unchanged; if Excel returns any kind of error, the second value appears. That value can be 0, empty text (""), a message or another formula.

It is used most for division by zero (unit cost when the quantity is 0), lookups that find nothing (VLOOKUP, MATCH) and ratios built on missing data. Showing 0 instead of the error keeps totals intact; showing a message makes the missing record stand out.

IFERROR catches every error, so a #NAME? from a typo or a #REF! from a deleted reference is hidden too. If you only want to catch "not found", IFNA is safer, and XLOOKUP takes its own not-found value as an argument.

Syntax

English Excel=IFERROR(value, value_if_error)
Turkish Excel=EĞERHATA(değer; hatalıysa)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
valueRequiredThe formula or value whose result you want, e.g. B2/C2.
value_if_errorRequiredThe value returned if value is an error: 0, "", "Not in list" or another formula.

Examples

1Show 0 instead of a divide-by-zero error

Unit cost is total cost divided by quantity. The product that was not made this month has a quantity of 0, so the division returns #DIV/0!; IFERROR shows 0 instead.

D2fx=IFERROR(B2/C2,0)
Show 0 instead of a divide-by-zero error: example table
ABCD
1ItemTotal CostQtyUnit Cost
2Notebook4,800.0012040.00
3Binder3,150.009035.00
4Eraser1,250.0000.00
5Stapler2,640.004855.00
  • D2 (filled down to D5)=IFERROR(B2/C2,0)

The eraser row shows 0.00 instead of an error; the other rows give their normal result, 40.00 for the notebook. Enter a quantity in C4 and the real cost is calculated by itself.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Growth rate for a product with no sales last year

The change is the difference between the two years divided by last year. For a product with no sales last year the division is by zero; IFERROR writes a note instead of the error.

D2fx=IFERROR((C2-B2)/B2,"New product")
Growth rate for a product with no sales last year: example table
ABCD
1ItemLast YearThis YearChange
2Notebook42,00046,20010.0%
3Pen38,50040,0404.0%
4Planner012,800New product
5Binder15,60017,16010.0%
  • D2 (filled down to D5)=IFERROR((C2-B2)/B2,"New product")

The planner shows New product; the notebook grew 10.0% and the pen 4.0%. Because the note is text, it is left out of the average of the change column.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3Catch an employee ID that is not found

The IDs are looked up in the staff list (D:F); INDEX and MATCH return the name. For an ID that is not in the list, MATCH returns #N/A and IFERROR turns it into a label.

B2fx=IFERROR(INDEX($E$2:$E$4,MATCH(A2,$D$2:$D$4,0)),"Unknown")
Catch an employee ID that is not found: example table
ABCDEF
1Employee IDFull NameEmployee IDFull NameDepartment
2P-1038Emma ClarkP-1038Emma ClarkAccounting
3P-1077UnknownP-1042Liam TurnerSales
4P-1042Liam TurnerP-1047Olivia HughesWarehouse
  • B2 (filled down to B4)=IFERROR(INDEX($E$2:$E$4,MATCH(A2,$D$2:$D$4,0)),"Unknown")

P-1077 is not on the staff list, so B3 shows Unknown; the other IDs return their names: Emma Clark, Liam Turner.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Hidden error

Cause: The formula has a typo or a deleted reference; IFERROR catches that too, shows the message, and the problem goes unnoticed.

Fix: Write and check the formula without IFERROR first; to catch only missing lookups, use IFNA.

Average too low

Cause: Errors were replaced with 0, and AVERAGE counts those zeros.

Fix: Return "" for rows that should not count; AVERAGE skips text.

Good to know

  • Use "" to make the cell look empty: =IFERROR(B2/C2,""). The cell appears blank but still holds the formula.
  • To add up a range while skipping errors, use =SUM(IFERROR(C2:C9,0)); it works directly in Microsoft 365 and Excel 2021, and with Ctrl+Shift+Enter in older versions.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add a unit cost column to the product table, and show 0 instead of an error where the quantity is 0

Convert to table

Bring your table into the tool and type this. Nexell calculates the unit cost with real Excel formulas and shows it in the preview; in the downloaded file, the real cost replaces the 0 as soon as a quantity is entered.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between IFERROR and IFNA?

IFERROR catches every error type; IFNA catches only #N/A and shows the others as they are. In lookup formulas, IFNA hides the not-found case while still letting you see a real mistake in the formula.

How do I hide a VLOOKUP error with IFERROR?

Put the lookup inside IFERROR: =IFERROR(VLOOKUP(A2,$F$2:$G$9,2,FALSE),"Not in list"). When the code is missing, "Not in list" appears instead of #N/A.

Related functions

All logical functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free