IFERROR in Excel: Syntax, Examples, and When Not to Use It

IFERROR replaces a formula's error with whatever you tell it to show instead, using this syntax: =IFERROR(value, value_if_error). So =IFERROR(VLOOKUP(A2,Prices,2,FALSE), "Not found") runs the VLOOKUP, and if that VLOOKUP returns an error, the cell shows "Not found" rather than #N/A. If the formula works, you get the normal result and IFERROR does nothing at all.

It exists because a single error poisons everything downstream. One #N/A in a column makes SUM return #N/A for the whole column, and a report with an error cascading through it is unusable even though 499 of the 500 rows are fine. IFERROR contains the damage to the one cell that actually has a problem.

Which errors does IFERROR catch?

All seven of Excel's error values: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL!. That is a genuinely useful thing to know, because it means IFERROR does not care why the formula broke. It catches everything.

Catching all seven is IFERROR's strength and its trap, and the section further down explains why.

How do I use IFERROR with VLOOKUP?

This is the pairing IFERROR is mostly written for. Wrap the whole lookup, and put the fallback second:

=IFERROR(VLOOKUP(A2, $E$2:$F$200, 2, FALSE), "Uncategorized")

Now codes that exist in your master list get their category, and codes that do not get the word Uncategorized instead of #N/A. A column of Uncategorized rows is something you can filter and work through. A column of #N/A is something you have to read past.

Return a blank with "" as the fallback if the sheet is going to a client and empty reads better than a label. Return a zero with 0 if the result feeds a SUM, because "" is text and text in a SUM range is ignored, which is usually what you want, but zero makes the intent explicit for anyone reading the formula later.

The same wrapper works on XLOOKUP, though XLOOKUP has this built in: its fourth argument, if_not_found, does the same job without a wrapper. =XLOOKUP(A2, E:E, F:F, "Uncategorized") is cleaner than wrapping XLOOKUP in IFERROR, and it only catches the not-found case, which is what you actually meant.

How do I stop #DIV/0! from showing?

Division by an empty or zero cell is the other classic. A percent change column comparing this month to last month throws #DIV/0! on every row where last month is blank:

=IFERROR((C2-B2)/B2, "")

That returns an empty-looking cell instead of the error. It is safe here in a way it is not with lookups, because there is only one realistic reason that formula errors: B2 is zero or blank. You are not hiding an unknown problem, you are handling a known one.

If you want the distinction to stay visible, IF is more honest: =IF(B2=0, "No prior", (C2-B2)/B2) says out loud what the blank means. Worth doing on anything going to someone else, since a blank cell and a genuinely zero change look the same. There is more on building conditional logic with IF if the rules get more involved than one test.

What is the difference between IFERROR and IFNA?

IFNA catches only #N/A. Everything else passes straight through and still shows as an error. IFERROR catches all seven.

On a VLOOKUP, IFNA is usually the better choice, and the reason is worth being precise about. If you typo the column index and ask for column 7 of a 2-column range, VLOOKUP returns #REF!. Under IFERROR that broken formula shows "Uncategorized" and looks like it is working. Under IFNA the #REF! shows through, and you fix the formula you actually broke. IFNA hides the expected failure and exposes the unexpected one, which is the behavior you want.

Why is IFERROR making my numbers wrong?

Because it is doing exactly what you asked. This is the real hazard, and it is not subtle once you have been bitten by it.

Say you wrap a whole column of lookups in IFERROR(..., 0) and then SUM the column. Every row that failed contributes zero. The total comes out clean, with no errors anywhere, and it is wrong by however much those failed rows should have been. Nothing on the sheet indicates a problem. An #N/A at least announces itself.

The rule that keeps you out of this: use IFERROR when you know the specific reason a formula can error and that reason is acceptable. Do not use it as a way to make a messy sheet look tidy. If you cannot name the failure it is catching, you should not be catching it yet.

A useful habit is to build the column without IFERROR first, look at what breaks, fix what is fixable, and only then wrap the residue you have decided to live with. On converted data the errors you find in that first pass are almost always fixable, which is the point of the next section.

Fix the cause before you wrap the symptom

After a PDF conversion, most errors have one of three causes, and all three are worth fixing rather than hiding:

Numbers stored as text. The amount column is left-aligned and SUM returns zero, or a lookup on an account number returns #N/A because the text "1042" is not the number 1042. This is the single most common one. Converting the text to real numbers makes the errors disappear at the source, and the totals become correct rather than merely error-free.

Stray spaces. A trailing space or a non-breaking space that TRIM alone will not touch makes two identical-looking values unequal. Stripping the spaces properly resolves a whole column of #N/A at once, and it is usually one formula.

Rows that did not extract cleanly. If the source PDF has merged columns or a layout the extractor guessed at, some rows arrive misaligned and no formula can rescue them. Converting the file properly in the first place is the fix. Our PDF to Excel converter preserves the table structure so the columns land where they belong, which removes the class of error entirely rather than papering over it. If the documents are bank statements, running them through a tool that turns the statement into clean spreadsheet rows gets you the same result without the manual repair pass.

Work through those three, and the number of cells still erroring usually drops to the handful that genuinely have no match. Those are the ones IFERROR is for.

Can I use IFERROR with more than one formula?

Yes, by nesting: IFERROR's second argument can be another formula, so it becomes a fallback chain. Try the first lookup, and if it fails, try a second one:

=IFERROR(VLOOKUP(A2, Table1, 2, FALSE), IFERROR(VLOOKUP(A2, Table2, 2, FALSE), "Not found"))

That checks one master list, falls back to another, and only then gives up. It reads badly past two levels, so if you need three or more, an INDEX MATCH against a combined list is easier to maintain than a stack of nested IFERRORs.

IFERROR also works on arrays. If value is an array formula, IFERROR returns an array of results, one per cell in the range, so a spilled XLOOKUP or FILTER wrapped in IFERROR handles errors across the whole spill rather than only the first cell.

Does IFERROR slow the workbook down?

Slightly, and only in a way that matters at scale. IFERROR evaluates the inner formula, and if it errors, evaluates it again to produce the fallback. On a few hundred rows this is invisible. On tens of thousands of nested lookups it is measurable, because you have doubled the work on every failing row.

If a large sheet feels sluggish and it is full of IFERROR-wrapped lookups, the fix is not usually to remove IFERROR. It is to notice that a lot of rows are failing, which is telling you something about the data. Fast and wrong is not the goal.

The short version

=IFERROR(formula, fallback) catches all seven Excel errors. Prefer IFNA on lookups so a broken formula still announces itself. Use XLOOKUP's if_not_found argument instead of a wrapper when you have it. Never wrap a column in IFERROR(...,0) and then SUM it without knowing what failed. And on converted data, fix the numbers-as-text and the stray spaces first, because most of the errors you were about to hide will fix themselves.

Last updated July 2026.