Featured image
IFERROR in Excel
IFERROR returns a replacement when a formula produces any error at all: =IFERROR(value, value_if_error). It is the quickest way to stop a report being littered with #N/A, and it is also the quickest way to hide a #REF! that means the formula is pointing at the wrong place. For lookups, IFNA is usually the better choice.
=IFERROR(value, value_if_error)
IFERROR is a tidying function, and tidying is the problem with it. It does not fix anything. It catches whatever the formula inside it produced and shows something friendlier, and it makes no distinction between an error that is a normal outcome and an error that is a defect.
That distinction is worth caring about. A lookup finding nothing is ordinary: the name is not on the list and never was. A lookup pointing past the edge of its table is a broken formula. IFERROR treats both as the same event and prints the same reassuring blank for each.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The formula to try. Usually a lookup or a division, the two things that most often fail for ordinary reasons.
- What to show if it failed. Often "" for a blank or 0 for something that will be added up later. It can be another formula, which is how a fallback lookup against a second table gets written.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Catch a division by zero
=IFERROR(D2/0, "No figure")Returns No figure
Dividing by zero is the textbook case for IFERROR, because there is nothing wrong with the formula. The denominator is legitimately zero and the sheet should say so in words rather than in punctuation.
Return zero when a lookup finds nothing
=IFERROR(VLOOKUP("Gus", A2:D7, 4, FALSE), 0)Returns 0
Zero rather than a blank, because this column is going to be totalled and a cell containing "" is text. It is worth being deliberate about which: blanks look better and zeros add up.
The same wrapper hiding a genuine fault
=IFERROR(VLOOKUP("Chen", A2:D7, 9, FALSE), 0)Returns 0
Chen is in the table. The formula asks for the ninth column of a four-column range, which is a #REF! and a real mistake, and IFERROR turns it into a quiet zero that will be summed with everything else. Nothing on the sheet now indicates anything is wrong.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
IFERROR against IFNA
IFNA is IFERROR with a narrower mouth. It catches #N/A and nothing else, so a lookup that found nothing is handled and a lookup that is structurally broken still shows as broken.
=IFNA(VLOOKUP("Gus", A2:D7, 4, FALSE), "Not on the list")Returns Not on the list. Handles the ordinary not-found case.
=IFNA(VLOOKUP("Chen", A2:D7, 9, FALSE), "Not on the list")Returns #REF!. And leaves the broken one visible, which is the entire point. Around a lookup, reach for IFNA first.
Wrap the smallest thing you can
An IFERROR around an entire long formula tells you only that something in it failed. Around the one part that can legitimately fail, it keeps the rest of the formula reporting for itself.
- Around a division whose denominator can be zero, rather than around the calculation the division sits inside.
- Around a lookup, and preferably as IFNA.
- Not around a whole chain of nested functions, where it becomes a way of never finding out which link broke.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=IFERROR(VLOOKUP("Gus", A2:D7, 4, FALSE), 1/0)WhyIFERROR does not produce errors of its own, so one showing through means the replacement value is itself an error. The lookup failed as expected, the wrapper reached for its fallback, and the fallback failed too.
FixCheck what value_if_error actually evaluates to. A second lookup used as a fallback can fail in its own right, and the outer IFERROR will dutifully return that failure rather than catching it.
=IFERROR(VLOOKUP("Chen", A2:D7, 0, FALSE), 0) + "text"WhyAn error after the IFERROR rather than inside it. The wrapper caught the lookup; the arithmetic around it is what failed.
FixCheck what surrounds the IFERROR as well as what it contains. The function only protects its own first argument.
What goes wrong
These are the mistakes that come up again and again with IFERROR. Each one is reasonable. That is why people make it.
Wrapping every formula on the sheet in IFERROR as a habit.
WhyThe sheet looks clean, and errors feel like something to be removed.
InsteadUse it where an error is a normal outcome. Elsewhere an error value is a free diagnostic that something needs attention, and silencing it does not make the number underneath correct.
Using IFERROR around a lookup when IFNA was meant.
WhyIFERROR is better known and types more readily.
InsteadIFNA for lookups. It handles not found and leaves #REF! and #VALUE! visible.
Returning "" into a column that gets totalled.
WhyA blank looks neater than a zero.
InsteadA cell holding "" is text, not an empty cell: COUNT will skip it, COUNTA will count it, and ISBLANK says FALSE. If the column is arithmetic, return 0.
Common questions
What is the difference between IFERROR and IFNA?
IFERROR catches every error value. IFNA catches only #N/A. Around a lookup, IFNA is safer, because it handles the ordinary not-found case while still letting a broken reference announce itself.
How do I hide #N/A in Excel?
Wrap the formula in IFNA with the message you want: =IFNA(your formula, ""). Before you do, check that the #N/A really is a missing row rather than a trailing space or a number stored as text, because hiding it does not make the lookup match.
Does IFERROR slow a workbook down?
Slightly, because the inner formula has to be evaluated before the wrapper can decide. On tens of thousands of lookups that becomes measurable. It is rarely the first thing worth optimising.
Can IFERROR return another formula?
Yes. A common pattern is a second lookup as the fallback, trying a different table when the first one has no match. Nest no further than that; three chained fallbacks are impossible to debug.