FormuladrillIFERROR
IFERROR(value, value_if_error) returns value if it works and value_if_error if it produces any error (#N/A, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NULL!). Typical use: =IFERROR(A2/B2, 0) or wrapping a lookup to show "Not found" instead of #N/A.
Syntax
IFERROR(value, value_if_error)| Part | What it is |
|---|---|
value | The formula or reference to evaluate. |
value_if_error | What to return if value is an error: 0, "", "Not found" or another formula. |
Examples
=IFERROR(A2/B2, 0)Result: A2 divided by B2, or 0 when B2 is empty or zero
=IFERROR(VLOOKUP(E2, A:C, 3, FALSE), "Not found")Result: The lookup result, or Not found when E2 is missing
=IFERROR(VALUE(A2), "")Result: A2 as a number, or an empty cell when A2 is not numeric text
=IFERROR(XLOOKUP(E2, A:A, B:B), XLOOKUP(E2, Archive!A:A, Archive!B:B))Result: Looks in the archive sheet only when the first lookup fails
Common mistakes
- Hiding real mistakes: IFERROR around a whole formula masks typos and #REF! too. Use IFNA for lookups so only not-found cases are caught.
- Totals look wrong after returning "": the empty string is text and breaks later arithmetic. Return 0 when the column feeds a SUM, or use AVERAGE which ignores text.
- IFERROR cannot catch a dependency that is not evaluated inside it; wrap the formula that actually errors.
- #NAME? in Excel 2003 or earlier: IFERROR needs Excel 2007 or later; use IF(ISERROR(x), y, x).
Practice IFERROR
These drills check your answer on live data, free, no account needed.
- No more #DIV/0!Revenue per unit is revenue divided by units. When units are 0 the cell must show 0 instead of an error. Fill D2 and D3.
Questions
IFERROR or IFNA?
IFNA only replaces #N/A, which is what lookups return when a value is missing. It leaves other errors visible so you still see broken references. Prefer IFNA around VLOOKUP, XLOOKUP and MATCH.
How do I replace an error with a blank cell?
=IFERROR(formula, ""). The result is an empty string, which displays as blank but counts as text in COUNTA.
Can IFERROR fix #SPILL!?
No. #SPILL! means the cells a dynamic array needs are not empty; clear them instead.
Related
- ISERROR, ISBLANK and IS functionsHow the Excel IS functions (ISBLANK, ISERROR, ISNA, ISNUMBER, ISTEXT) return TRUE or FALSE about a cell, with examples inside IF and the empty string trap.
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
- IFHow the Excel IF function returns one value when a test is true and another when false, with pass or fail, blank cell, nested IF and IF with AND examples.