How to Use IFERROR in Excel, and What It Quietly Hides

1. What an error is, and the seven you will meet

An Excel error is not a crash and it is not a warning. It is a value, in the same way that 42 and "Tuesday" are values. It sits in the cell, it flows into anything that reads that cell, and one of it in a range is enough to turn a total into an error too.

Each one names a different problem, and reading the name is usually faster than debugging. Here are the seven you will actually meet, each produced by running a formula that causes it.

Before reading on, pick out which one of those seven is a statement about your data and which are statements about your formula. That split is the whole argument of this page.

Only #N/A is really about the data: it says a key you looked for is not in the list, which may be perfectly normal. The other six say the formula is asking for something impossible. #REF! in particular means the formula is now broken in a way that will never fix itself. Those are not the same kind of news, and a tool that treats them the same is a tool to use carefully.

2. The syntax, and where the formula goes

Two arguments. The thing you want, and what to show if the thing you want comes back as an error.

=IFERROR( your_formula , what_to_show_instead )

So a lookup that might miss becomes:

=IFERROR(INDEX(Costs[Cost], MATCH([@Product], Costs[Product], 0)), 0)

Excel runs the inner formula once. If the answer is any value at all, you get that answer. If the answer is one of the seven errors above, you get your replacement. There is no third case.

One detail worth having straight: IFERROR does not fix anything. The lookup still failed. The division by zero still happened. All that changed is what is printed in the cell, and everything downstream now reads your replacement as if it were a real measurement.

3. What to put in the second argument: 0, blank, or a word

Before the explanation: twelve cost lookups succeed and four fail. Filling the four failures with 0 and filling them with "" both look like tidying up. Say whether you would expect the column's average to come out the same either way.

It does not. Both were run on the same twelve values, which total 1,240.

=AVERAGE(  filled with 0  )        77.5
=AVERAGE( filled with "" )        103.3333

Filling with 0 gives Excel sixteen numbers, so it divides 1,240 by 16 and gets 77.5. Filling with "" gives Excel twelve numbers and four pieces of text, and AVERAGE skips text, so it divides 1,240 by 12 and gets 103.33. Neither is a rounding difference. They are answers to two different questions, and the wrapper you typed chose which question got asked.

There is a further wrinkle in "" worth knowing, because it catches people who test for emptiness. A cell holding "" is not empty; it holds a piece of text that happens to have no characters. Run in Excel on such a cell:

=ISBLANK(the cell)      FALSE
=COUNT(the column)      12
=COUNTA(the column)     16

COUNT counts numbers, so it says 12. COUNTA counts anything that is not empty, and since "" is not empty it says 16. If a "count of records" on your sheet looks too high by exactly the number of failed lookups, this is why.

So, the three choices and what each one is actually claiming.

The rule underneath is short: never replace an error with a number unless that number is a measurement you would defend. A zero that means "we do not know" gets averaged, charted and summed exactly like a zero that means "none", and nobody downstream can tell them apart.

4. The four rows that vanished, worked end to end

Here is the whole risk as a single worked case, on the sixteen-row orders table used across this set of guides. Total revenue is 9,890 and there are three products: desks, chairs and lamps.

A cost list arrives from the finance system. It has desks and chairs on it. Lamps are missing, because lamps were set up in a different category last quarter and nobody mentioned it. So the cost lookup succeeds on twelve rows and returns #N/A on four. Wrapped the usual way:

=IFERROR(INDEX(CostList[Cost], MATCH([@Product], CostList[Product], 0)), 0)

The sheet now looks completely clean. Not one error message anywhere. Here is what it reports, next to what is true.

Check the gap by hand: the missing lamp cost is 40 units at 22 each, which is 880, and 6,076 − 880 = 5,196. That single unstated 880 moves the reported margin by nearly nine percentage points, and it moves it in the flattering direction, which is the direction nobody questions.

Notice that every individual number on that sheet is arithmetically correct. The sum really is the sum of what is there. Nothing needs to be miscalculated for a report to be wrong; it is enough for something to be missing and for the missingness to be invisible. Without the IFERROR, the total would have shown #N/A and somebody would have asked why within a minute.

Now picture your own most-forwarded workbook. If four of its rows silently failed a lookup this month, which number on the summary tab would move, and would anyone be able to tell from looking at it?

5. Count the errors before you hide them

The fix is not to leave errors on the page forever. It is to make the count of them a number you deliberately produce and read.

=SUMPRODUCT(--ISNA(C2:C17))     4      unmatched lookups
=COUNT(C2:C17)                  12     rows that got a value
=SUM(C2:C17)                    #N/A   the whole total, poisoned by one

That last line is worth sitting with. A single error in a range makes SUM return an error. People experience that as Excel being obstructive, and it is the most useful thing Excel does here: it refuses to add up a column it cannot fully see, and it refuses loudly. IFERROR does not solve that problem, it silences the alarm attached to it.

So the working order is: count first, understand what the misses are, decide what they mean, and only then choose what to display. If the four unmatched rows are genuinely nothing, a zero is honest. If they are lamps that cost 880, a zero is a false statement about the business. The formula cannot tell the difference; you can.

When the misses are real and you have to ship anyway, the count belongs in writing beside the result, not just in your head. That is a habit with its own page: documenting data limitations covers how to say "4 of 16 orders have no cost on file, covering 40 units" without turning the report into an apology.

6. IFNA: the narrower net

Before the explanation: you wrap a lookup column in IFERROR to hide the unmatched keys. A month later somebody deletes a column and one of those formulas breaks with #REF!. Say what appears on your sheet.

Your replacement value. The broken formula looks exactly like an ordinary unmatched key. Here is the run.

=IFERROR(#REF! * 2, "hidden")     hidden
=IFNA(   #REF! * 2, "hidden")     #REF!

IFNA takes the same two arguments and catches only #N/A. Everything else passes straight through and stays visible. On a lookup that is the right instrument almost every time, because #N/A is the one error a lookup is entitled to produce. A #REF!, a #VALUE! or a #NAME? coming out of a lookup means something is broken, and you want to be told.

Both still hide the unmatched key itself, which is the case you meant to handle:

=IFERROR(MATCH("Sofa", Products[Product], 0), "hidden")     hidden
=IFNA(   MATCH("Sofa", Products[Product], 0), "hidden")     hidden

So the choice is simple. On anything that is a lookup, reach for IFNA. Keep IFERROR for the cases where the error genuinely is expected and is not #N/A, the commonest being a division whose denominator can legitimately be zero.

That division case deserves its own sentence, because zero and unknown are different there too. =IFERROR(A2/B2, 0) on a month with no orders reports a conversion rate of 0%, which reads as "we converted nobody" rather than "there was nobody to convert". If that distinction matters to your reader, say so: =IF(B2=0, "no orders", A2/B2) costs the same to type and cannot be misread.

7. The old ISERROR pattern, and why IFERROR replaced it

You will meet this shape in inherited workbooks, and it is worth being able to read.

=IF(ISERROR(VLOOKUP(A2,Costs,2,FALSE)), 0, VLOOKUP(A2,Costs,2,FALSE))

It does the same job the long way round. The lookup is written twice: once to test whether it fails, once to produce the answer when it does not. That is worse on three counts. It is twice as much to read. Excel computes the lookup twice rather than once, which is invisible on a hundred rows and noticeable on fifty thousand. And any future edit has to be made in two places, so the two copies drift apart and the sheet starts testing one formula and returning another.

=IFERROR(VLOOKUP(A2,Costs,2,FALSE), 0)

Same behaviour, one copy of the lookup. When you find the old pattern, this replacement is safe and mechanical. The relatives ISERR, ISNA and IFERROR all still have their uses, but ISNA earns its keep in a counting formula, as in section five, rather than wrapped around the thing it is testing.

8. Where the wrapper goes: at the edge, not in the middle

A wrapper is a decision about presentation, so it belongs where the sheet is presented, not in the middle of a calculation chain.

Put IFERROR around the last step and the intermediate columns stay honest. You can still see which rows failed, you can still count them, and only the final display column is smoothed. Put it around an early step instead and the failure is converted into a plausible number at row level, then carried through every subsequent column, and by the time anyone looks there is nothing left to find.

There is a related habit worth adopting from the same instinct: keep a small check block on any sheet that matters, off to one side, holding the row count, the error count and the total, all as formulas. Then a broken lookup shows up as a number that moved rather than as a colour nobody noticed. Checking your work before anyone else does is the fuller version of that idea.

Edge cases that break error handling quietly

Six that each cost somebody an afternoon.

IFERROR around the whole formula when only one part can fail. Wrapping A2*VLOOKUP(...) means a text value in A2 also gets hidden. Wrap the part that is allowed to fail, not the sentence it sits in.

Zero-filled errors inside an average. A mean over a column where failures became zeros is dragged toward zero by exactly the failures you could not see. This is the same problem NULLs cause in a database, and the SQL version of it is worth reading because the mechanism is identical.

"" counted as a record. COUNTA and many pivot counts treat an empty string as present. Row counts come out right by luck and wrong by a few whenever the failure rate changes.

An IFERROR that has outlived its reason. Somebody wrapped a formula in 2023 because of a one-off import problem. The wrapper is still there and is now hiding a real failure that started in June. If you cannot say what error a wrapper exists to catch, it should not be there.

Errors inside conditional formatting and charts. A hidden error becomes a zero-height bar or an uncoloured cell, both of which read as a genuine low value. The chart is the last place you want a silent substitution.

IFERROR on a spilled array. Wrapping a dynamic array function returns the replacement for the entire spill rather than cell by cell, so one bad row can blank out the whole result. Check what the wrapper covers before you trust it.