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
=IFERROR(value, value_if_error)=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
| Argument | Required | Description |
|---|---|---|
value | Required | The formula or value whose result you want, e.g. B2/C2. |
value_if_error | Required | The 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.
=IFERROR(B2/C2,0)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Total Cost | Qty | Unit Cost |
| 2 | Notebook | 4,800.00 | 120 | 40.00 |
| 3 | Binder | 3,150.00 | 90 | 35.00 |
| 4 | Eraser | 1,250.00 | 0 | 0.00 |
| 5 | Stapler | 2,640.00 | 48 | 55.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.
=IFERROR((C2-B2)/B2,"New product")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Last Year | This Year | Change |
| 2 | Notebook | 42,000 | 46,200 | 10.0% |
| 3 | Pen | 38,500 | 40,040 | 4.0% |
| 4 | Planner | 0 | 12,800 | New product |
| 5 | Binder | 15,600 | 17,160 | 10.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.
=IFERROR(INDEX($E$2:$E$4,MATCH(A2,$D$2:$D$4,0)),"Unknown")| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee ID | Full Name | Employee ID | Full Name | Department | |
| 2 | P-1038 | Emma Clark | P-1038 | Emma Clark | Accounting | |
| 3 | P-1077 | Unknown | P-1042 | Liam Turner | Sales | |
| 4 | P-1042 | Liam Turner | P-1047 | Olivia Hughes | Warehouse |
- 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
Add a unit cost column to the product table, and show 0 instead of an error where the quantity is 0
Convert to tableBring 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
- IFNAEĞERYOKSA · Returns a fallback if the value is #N/A.
- IFEĞER · Returns one value if a condition is true and another if false.
- ISERROREHATALIYSA · TRUE if the value is any error.
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.