IFERROR Generator
Show a friendly value instead of #N/A, #DIV/0! or any other error a formula produces.
IFERROR
Show a friendly value instead of #N/A, #DIV/0! or any other error a formula produces.
The expression that may fail. Paste it without its leading equals sign.
Shown whenever the formula returns any error. Text needs quotes; use "" for a blank-looking cell or 0 for a number.
=IFERROR(VLOOKUP(A2, $D$2:$F$100, 3, FALSE), "Not found")Worked Example
Look up a product price and show "Not found" instead of #N/A when the code is missing from the table.
=IFERROR(VLOOKUP(A2, $D$2:$F$100, 3, FALSE), "Not found")Returns: Returns the value from the third column of D:F when A2 matches; returns the text "Not found" when the lookup fails for any reason.
Checks Before You Paste
- •IFERROR swallows every error type - #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!, plus newer ones such as #SPILL! - so a mistyped function name or a deleted column looks exactly like "no match found". Always confirm the inner formula works on a row you know should succeed before you wrap it.
- •IFERROR(...,"") does not create an empty cell: it returns a zero-length text string. ISBLANK stays FALSE, COUNTA counts it, and a line chart plots it as zero rather than leaving a gap. If a chart should skip those points, return NA() instead of "".
- •Excel requires both arguments - =IFERROR(A1/B1) is rejected as you type it. Google Sheets makes the second argument optional and defaults to blank, so a one-argument IFERROR written in Sheets is not valid Excel syntax and will not survive the trip into an .xlsx workbook. Type the second argument out if the file will ever be opened in Excel.
How IFERROR works
Availability: Excel 2007 and later, Excel for the web and Google Sheets. IFNA, the narrower alternative discussed below, needs Excel 2013 or later. AGGREGATE, another way to skip errors, needs Excel 2010.
IFERROR runs a formula, and if the result is any error value it returns your replacement instead. If the result is not an error it hands back the original value untouched. It replaced the older IF(ISERROR(x), fallback, x) pattern, which had to compute x twice.
The important word is any. IFERROR catches #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL!, plus #SPILL!, #CALC! and the other dynamic-array errors. That breadth is what makes it convenient and what makes it dangerous: a lookup wrapped in IFERROR reports "Not found" both when the key genuinely is not in the table and when you have deleted the column the formula pointed at. For lookups, IFNA is nearly always the better wrapper, because it intercepts only #N/A - the one error that means "no match" - and lets structural mistakes stay visible.
Choose the replacement value with the downstream formulas in mind. IFERROR(x, 0) is right when a missing value really is zero, and wrong when it should be excluded, because those zeros drag an AVERAGE down. IFERROR(x, "") produces a zero-length text string, not an empty cell: ISBLANK still reports FALSE, COUNTA still counts it, and any arithmetic on it returns #VALUE!. IFERROR(x, NA()) keeps the cell out of averages and out of chart lines, at the cost of looking like an error to a reader.
IFERROR also cannot tell you that an answer is wrong, only that it errored. An approximate-match VLOOKUP that quietly returns the row above the one you wanted produces a number, not an error, and sails straight through. Wrap the smallest failing piece rather than the whole calculation, and confirm the inner formula works on a row you know should succeed before adding the wrapper.
Syntax
=IFERROR(value, value_if_error)| Argument | Required | What it does |
|---|---|---|
value | Required | The formula or expression to evaluate. Written without its own leading equals sign, for example VLOOKUP(A2, $D$2:$F$100, 3, FALSE) or B2/C2. |
value_if_error | Required | What to return when value produces any error. Text in quotes, a number, a cell reference, another formula, "" for an empty-looking cell, or NA() to keep the cell out of averages and charts. Excel requires this argument; Google Sheets treats it as optional and defaults to blank. |
More worked examples
A percentage-change column where last month's figure in B2 can legitimately be zero.
=IFERROR((C2-B2)/B2, "n/a")Returns: B2 = 0 with C2 = 400 returns "n/a" instead of #DIV/0!. B2 = 250 with C2 = 300 returns 0.2.
The result column now mixes numbers and text, so a total below it needs SUM, which ignores text, rather than a plain addition of the cells.
A lookup where you want a genuine no-match handled but a broken reference to stay loud.
=IFNA(XLOOKUP(A2, $D$2:$D$500, $E$2:$E$500), "Not on list")Returns: An unknown code in A2 returns "Not on list". Delete column E and the formula shows #REF! rather than pretending the code was missing.
Swap IFNA for IFERROR here and both situations become the same message.
Trying two price tables in turn before giving up.
=IFERROR(VLOOKUP(A2, $G$2:$H$50, 2, FALSE), IFERROR(VLOOKUP(A2, $J$2:$K$50, 2, FALSE), "No price"))Returns: A code found in the first table returns its price; a code found only in the second returns that price; a code in neither returns "No price".
The nesting is fine, but each layer hides its own reasons for failing, so test the two lookups separately whenever the result surprises you.
Common mistakes
- It hides your own typos
- A misspelled function or a deleted range gives #NAME? or #REF!, and IFERROR turns both into "Not found" - so the report looks like it has data problems rather than a broken formula. Build and test the inner formula first, then wrap it, and prefer IFNA for anything lookup-shaped.
- "" is text, not an empty cell
- After =IFERROR(A2/B2, ""), ISBLANK returns FALSE, COUNTA counts the cell, and a later =D2*100 on it returns #VALUE!. If the column feeds arithmetic, return 0 or NA() instead, or filter the text out with N() or an ISNUMBER test.
- Zeros that quietly move the numbers
- =IFERROR(VLOOKUP(...), 0) inside a column you then average pulls the average toward zero for every missing row, and the AVERAGE gives no hint that it happened. Use "" or NA() when the value is unknown rather than genuinely nil, since AVERAGE skips both.
- Wrapping too much of the formula
- =IFERROR(VLOOKUP(A2, T, 3, FALSE) * $B$1 + C2, 0) returns 0 for a missing lookup, but also for a text value in C2 or a broken $B$1. Wrap only the part that can legitimately fail: =IFNA(VLOOKUP(A2, T, 3, FALSE), 0) * $B$1 + C2.
- Copying a one-argument IFERROR out of Google Sheets
- Sheets accepts =IFERROR(A1/B1) and defaults the fallback to blank. Excel rejects it while you are still typing. Always write the second argument if the formula will ever be opened in Excel or saved as .xlsx.
Frequently Asked Questions
Every kind. #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL! and the newer #SPILL!, #CALC! and #GETTING_DATA are all intercepted. That is exactly why a wrapped formula can look like it is reporting missing data when it is really reporting a broken reference.
Whenever the formula is a lookup - VLOOKUP, HLOOKUP, XLOOKUP, MATCH, INDEX/MATCH. #N/A is the error that means "this key is not in the table", which is the only case you want to translate into friendly text. Everything else is a fault in the formula, and IFNA lets it stay visible.
Because "" is a zero-length text string, and a cell containing a formula is never blank in Excel's sense anyway. Test with =A2="" rather than ISBLANK, or use COUNTBLANK, which does treat a formula's empty string as blank.
Not in Excel. Sheets makes value_if_error optional and returns a blank when it is omitted; Excel requires both arguments and refuses the formula. A Sheets file with one-argument IFERROR formulas will not survive an export to .xlsx unchanged.
No. Excel evaluates value once and reuses the result, which is the main reason IFERROR was added in 2007. The older IF(ISERROR(x), fallback, x) construction really did compute x twice, so replacing those speeds up heavy lookup columns.
Related Tools
VLOOKUP Generator
The formula most often wrapped in IFERROR, and the one that should use IFNA instead.
XLOOKUP Generator
Has a built-in if_not_found argument, so no wrapper is needed at all.
ISBLANK Generator
The IS family, including ISERROR and ISNA, for testing rather than replacing.
Excel Formula Explainer
Unwrap a nested formula to find which inner piece is actually erroring.