How to Use IFERROR in Excel

IFERROR lets a formula fail gracefully. You give it a formula and a backup value. If the formula works, you get its normal result. If it returns any error, such as #DIV/0! or #N/A, you get the backup value instead:

It's the quickest way to tidy a report full of error codes. It's also easy to overuse, because it hides real mistakes too. That's where IFNA, its more careful cousin, comes in.

The examples use this team budget in A1:D7. Actual is what has been spent so far, and Training's Actual cell, C6, is still empty:

#The IFERROR syntax

IFERROR checks value first. If it isn't an error, IFERROR returns it unchanged, so wrapping a working formula never changes its answer. Both arguments are required.

#Replace #DIV/0! with 0

Cost per unit is Actual divided by Units. For Software that works fine:

Supplies has no units, and Excel can't divide by zero:

Wrap the division in IFERROR and give it 0 as the fallback:

A result without an error passes straight through. You'll also get #DIV/0! when you divide by an empty cell, because Excel treats it as 0 in a calculation.

Your turn. Travel has no units yet either, so dividing its Actual by its Units fails. Fix it so the cell shows 0.

#Show a blank or a message instead

The fallback can also be text in double quotes. Training has no units either:

To make the cell look empty, use two double quotes with nothing between them:

That empty text has side effects, because the cell now holds text rather than nothing. If E7 contains the formula above:

SUM and AVERAGE skip text, so column totals still work, but arithmetic on the cell itself breaks. Use "" for cells people read, and 0 for results other formulas use.

#IFERROR with VLOOKUP

A lookup that can't find its value returns #N/A. There's no Utilities row in the budget:

When the value is there, you get the normal result. In a real sheet, point at a cell and lock the table before copying down:

If a value you can see in the list comes back as "Not found", look for an extra space or a number stored as text. The VLOOKUP guide has a checklist for #N/A.

IFERROR also won't help with blanks, because a blank isn't an error. Training is in the list, but its Actual cell is empty, and VLOOKUP returns an empty cell as 0:

That 0 makes it look as if Training spent nothing. If blanks matter, test for them with IF first.

The fallback can even be another lookup. If last year's categories are on a sheet named Archive, this tries the Archive list only when the first lookup fails:

#IFERROR with XLOOKUP

XLOOKUP has its own fourth argument, if_not_found, for exactly this job:

That's usually better than wrapping XLOOKUP in IFERROR, because if_not_found only steps in when there's no match. Other mistakes still show. Here the return range is one row short:

The first formula tells you something is wrong. The second claims Rent isn't in the budget, when it's in the very first row. See the XLOOKUP guide for its other optional arguments.

#IFNA: catch only #N/A

IFNA works like IFERROR, but it only replaces #N/A, the "not found" error. Every other error gets through:

Here's why that matters. This VLOOKUP has a mistake in it: A2:D7 has four columns, but the formula asks for the 5th.

Marketing is right there in row 5. IFERROR turns the #REF! into a believable but wrong "Not found". IFNA lets the #REF! through, so you spot the bad column number and fix it. When the value really is missing, both give the same answer:

So which should you use?

• VLOOKUP, or INDEX and MATCH: use IFNA. The INDEX and MATCH guide shows it in action.
• XLOOKUP: use its if_not_found argument.
• Calculations where one error is expected, like dividing by a cell that can be 0: IFERROR is fine, or test for the cause with IF, as shown below.

#Which errors IFERROR catches

IFERROR catches all of these. IFNA catches only #N/A.

The last two only appear in Microsoft 365 and Excel 2021 or later. Like XLOOKUP, FILTER has its own fallback argument: =FILTER(A2:A7, D2:D7>20, "None") returns None.

The one thing IFERROR can't fix is a blocked spill in its own cell. The formula itself worked, and the problem is the cells in the way, so clear them instead.

#Test for the cause with IF instead

When you know which error to expect, testing for its cause is often clearer. For a divisor that can be 0:

This only handles the zero. If C7 held text by mistake, you'd still see #VALUE!, which is what you want.

Older files may use =IF(ISERROR(C7/D7), 0, C7/D7), the way people wrote this before IFERROR. It repeats the formula, so swap in IFERROR when you can.

#Common IFERROR mistakes

#Which versions of Excel have IFERROR and IFNA?

IFERROR works in Excel 2007 and every version since, including Microsoft 365 and Excel for the web. IFNA arrived in Excel 2013, so use IFERROR if the file has to work in Excel 2010 or older. Google Sheets has both.

For more practice, the error handling topic has a free IFNA lookup, plus Premium exercises on IFERROR with a message and IFNA with MATCH.

#Key takeaways

• =IFERROR(value, value_if_error) returns the formula's result, or your fallback if the formula gives any error.
• Use 0 as the fallback when other formulas use the result, and "" only for display.
• For lookups, prefer IFNA or XLOOKUP's if_not_found, so real mistakes still show.
• IFERROR doesn't treat blanks as errors. A lookup that lands on an empty cell still returns 0.