IFERROR in Excel: Hide Errors Without Hiding Bugs
· 6 min read
IFERROR returns the result of a formula, unless that formula produces an error. Then it returns a value you choose instead, such as an empty cell, a zero or a message. Wrap it around the formula that might fail:
=IFERROR(value, value_if_error)For example, =IFERROR(C2/B2, "n/a") divides C2 by B2, and shows "n/a" instead of #DIV/0! when B2 is zero. IFERROR works in Excel 2007 and later, Excel for the web and Google Sheets.
Worked example: a #DIV/0! error
Average selling price per unit, by product:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Units | Revenue | Price per unit | Fallback 0 instead | IF version |
| 2 | Stapler | 40 | 480.00 | 12.00 | 12.00 | 12.00 |
| 3 | Paper ream | 0 | 0.00 | n/a | 0.00 | n/a |
| 4 | Kettle | 5 | 149.75 | 29.95 | 29.95 | 29.95 |
| 5 | Sponges | 120 | 96.00 | 0.80 | 0.80 | 0.80 |
| 6 | Average | 14.25 | 10.69 |
Without IFERROR, D3 would show #DIV/0!, because nothing was sold and you cannot divide by zero. D2 holds:
=IFERROR(C2/B2, "n/a")Filled down, the results are 12.00, n/a, 29.95 and 0.80.
What to put in value_if_error
The second argument decides what the reader sees, and it changes the maths further down the sheet:
""shows an empty-looking cell. Functions like AVERAGE and SUM skip it, because it is text."n/a"or"no sales"tells the reader why the value is missing. Also skipped by AVERAGE and SUM.0shows a zero. AVERAGE counts it.
That last point matters. The average of column D with "n/a" is (12.00 + 29.95 + 0.80) / 3 = 14.25. If D3 held 0 instead, as in column E, AVERAGE would divide by 4 and return 10.69. A product with no sales has no price per unit, so 0 is not a true value here, and it drags the average down.
IFERROR VLOOKUP
The most common use of IFERROR is wrapping a lookup, so a missing value shows a message instead of #N/A. Look up the price per unit for the product name in F2:
=IFERROR(VLOOKUP(F2, $A$2:$D$5, 4, FALSE), "Not found")With "Kettle" in F2 the result is 29.95. With "Mop", which is not in the table, VLOOKUP returns #N/A and IFERROR shows Not found.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Units | Revenue | Price per unit | Look up | IFERROR + VLOOKUP | XLOOKUP | |
| 2 | Stapler | 40 | 480.00 | 12.00 | Kettle | 29.95 | 29.95 | |
| 3 | Paper ream | 0 | 0.00 | n/a | Mop | Not found | Not found | |
| 4 | Kettle | 5 | 149.75 | 29.95 | ||||
| 5 | Sponges | 120 | 96.00 | 0.80 |
The $ signs keep the table in place if you copy the formula down. Our lookup comparison explains why the FALSE at the end matters.
The XLOOKUP way
XLOOKUP (Excel 2021, Microsoft 365 and Excel for the web) has a "not found" argument built in, so you do not need IFERROR at all:
=XLOOKUP(F2, A2:A5, D2:D5, "Not found")IFERROR vs IFNA
IFERROR catches every error type: #N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. IFNA catches only #N/A, which is the "not found" error from lookups. IFNA is available in Excel 2013 and later.
=IFNA(VLOOKUP(F2, $A$2:$D$5, 4, FALSE), "Not found")For lookups, IFNA is usually the better choice. Here is why. Suppose the function name has a typo:
=IFERROR(VLOKUP(F2, $A$2:$D$5, 4, FALSE), "Not found")Excel cannot find a function called VLOKUP, so the inner part returns #NAME?. IFERROR catches that and shows Not found for every row, including Kettle, which is in the table. The sheet looks tidy and is completely wrong.
With IFNA, the same typo shows #NAME?, and you fix it in seconds. The same goes for a column number that is too large (VLOOKUP returns #REF!) or a deleted source sheet. You want to see those errors.
A good rule:
| Situation | Use |
|---|---|
| A lookup where "not found" is a normal outcome | IFNA, or XLOOKUP's fourth argument |
| A division where the denominator can be zero | IF that tests the zero, or IFERROR |
| You have not worked out why the error happens | Neither yet. Find the cause first. |
Fixing the div/0 error without IFERROR
#DIV/0! appears when a formula divides by zero or by an empty cell. IFERROR is a quick fix, but an IF that tests the actual cause is clearer to anyone reading the formula:
=IF(B2=0, "n/a", C2/B2)This gives the same results as the IFERROR version in our example (column F of the first sheet). The difference shows up when something else goes wrong. If B2 contains text by mistake, the IF version returns #VALUE! and you notice. The IFERROR version would show "n/a" and hide the bad data. Type a word into B2 of the first sheet to see both.
Note that B2=0 is also TRUE when B2 is empty, so this catches blank cells too.
For percent changes with a zero starting value, the same thinking applies. See the percent change formula guide.
Common mistakes
Wrapping everything in IFERROR(..., 0). This is a very common pattern in formulas written by AI assistants. Ask for a sheet "without errors" and you may get IFERROR around every formula, often returning 0. The sheet looks clean, but real problems become zeros that flow into totals and averages, like the 10.69 above. When you review AI-written formulas, treat every IFERROR as a question: what error is it hiding, and should that error be possible at all? Temporarily remove the IFERROR from one cell and see what is underneath. The free Excel formula checker shows the value inside every IFERROR, so you can see what it hides.
Using IFERROR on a lookup with a typo or wrong range. As shown above, IFERROR turns #NAME? and #REF! into "Not found". If a lookup says "Not found" for a value you can see in the table, the IFERROR is hiding a different error. Common causes: a misspelled function, a column number beyond the table, or a missing FALSE.
Hiding errors that mean the data is wrong. A #VALUE! because a number is stored as text is information. Fix the data rather than covering it.
Returning text where a number is needed. If a later formula multiplies the result, "n/a" will cause a #VALUE! there. Decide what the next step expects before choosing the value_if_error.
Getting the argument order wrong. IFERROR takes the formula first and the fallback second. =IFERROR("n/a", C2/B2) always shows "n/a".
FAQ
What does IFERROR do in Excel?
It returns the formula's result when there is no error, and your chosen value when there is one. =IFERROR(C2/B2, "") shows an empty result instead of #DIV/0!.
How do I use IFERROR with VLOOKUP?
Wrap the whole VLOOKUP: =IFERROR(VLOOKUP(F2, $A$2:$D$5, 4, FALSE), "Not found"). For lookups, IFNA is usually safer because it only catches "not found".
What is the difference between IFERROR and IFNA?
IFERROR catches all errors. IFNA only catches #N/A. Use IFNA when "not found" is the only error you expect, so other problems stay visible.
How do I get rid of the #DIV/0! error?
Test the denominator with IF, for example =IF(B2=0, "", C2/B2), or wrap the division in IFERROR. Before you do, check that a zero or empty denominator is a real situation and not missing data.
Should I return 0 or blank from IFERROR?
Return blank or a short label when the value does not exist (no sales, no target). Return 0 only when zero is the true answer. Remember that AVERAGE counts zeros but skips text.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “West: % of target” →