Excel IFERROR: How It Works, Examples, and Common Uses

Excel’s IFERROR checks a value or formula and returns that result if it is not an error; if it is an error, it returns a replacement you specify. For example, with revenue in A2 and units in B2, =IFERROR(A2/B2,"No units") displays “No units” when B2 is zero instead of showing a division error.

What IFERROR does

IFERROR is an error-handling function: it lets a formula produce a useful alternative when its calculation fails. Its syntax is =IFERROR(value, value_if_error). The first argument is the calculation or value to check; the second is what Excel should return if the first argument evaluates to an error.

If the first argument evaluates normally, IFERROR returns its result unchanged. That result might be a number, text, a date, a logical value such as TRUE, or a blank. The replacement is used only when Excel detects an error, such as #DIV/0! or #N/A. It is not a general test for whether a cell is empty, whether a number is zero, or whether a result is undesirable.

The function is available in Excel 2007 and later, including Microsoft 365 for Windows. It is commonly used to make reports easier to read, handle expected missing matches, and prevent an error from disrupting a larger calculation. It does not fix the underlying cause: it changes what the worksheet displays when the specified calculation returns an error.

How Excel evaluates the two arguments

Excel evaluates the first argument and checks whether its result is an error value. If it is not, the function returns that result. If it is an error, Excel returns the second argument instead. The replacement can be text, a number, a reference, or another calculation.

IFERROR can catch errors including #DIV/0! (division by zero), #N/A (a value is unavailable), #VALUE! (an argument has the wrong type), #REF! (an invalid reference), #NAME? (unrecognized text in a formula), and #NUM! (a numeric calculation is invalid). It can also catch other Excel error values, including errors produced by newer dynamic-array features.

The second argument is not a message that Excel adds alongside the error. It replaces the error result. If that replacement calculation also produces an error, the new error can still appear. This matters when the fallback is itself a formula: IFERROR is not a guarantee that every possible outcome will be error-free.

Practical IFERROR examples

For a division calculation, suppose A2 contains revenue and B2 contains units sold. If A2 is 1200 and B2 is 0, the calculation cannot divide by zero, so this formula returns the text “No units”:

With revenue in A2 and units in B2, enter =IFERROR(A2/B2,"No units"). When B2 is 0, the result is No units; when B2 is 10, the result is 120.

Some reports need an apparently empty cell instead of a message. With a numerator in A2 and denominator in B2, enter =IFERROR(A2/B2,""). If B2 is zero, Excel returns an empty text string, so the cell looks blank. It is not the same as an actually empty cell: the formula remains in the cell, and other formulas may treat the empty text differently from a truly blank cell.

Another common use is to show a readable message when a lookup does not find a match. Suppose B2 contains the item to look up, and the lookup table is in D2:E100, with item names in column D and results in column E. Use =IFERROR(VLOOKUP(B2,$D$2:$E$100,2,FALSE),"Not found"). The final argument, FALSE, requests an exact match. If the lookup produces an error, the cell displays “Not found”; if it succeeds, the matching value from column E is returned.

Be careful with that lookup example: IFERROR replaces every error from the lookup, not just “no match.” A broken reference or another problem can also turn into “Not found,” making the actual fault harder to spot. If the only expected problem is a missing match, a narrower error handler may be a better choice.

Choosing a useful fallback

The right replacement depends on what the error means in the context of the worksheet. A message is often clearest for people reading a report. A blank-looking result can make a compact table cleaner, but it can also conceal missing data. Returning zero is appropriate only when zero is a meaningful substitute—not merely because it makes a chart or total look tidy.

Before choosing the fallback, ask what a reader or a later formula should understand. For a per-unit calculation, zero units may mean “not applicable,” rather than a rate of zero. For a lookup, a missing product may be a data-quality issue that someone needs to correct. In those cases, a descriptive label can preserve useful context better than an empty string or zero.

Use a message when a person should know why the calculation has no result, such as “Check denominator” or “Not found.”
Use an empty string when an empty-looking display is genuinely helpful and the missing result does not need to be investigated.
Use zero only when zero is an honest value for the situation and downstream calculations should treat it as zero.
Use a specific error handler when you want to replace one expected error but leave other errors visible.

A fallback is part of the meaning of a formula, not just its appearance. For example, replacing a failed calculation with zero can lower an average or total, while replacing it with text can affect how other formulas handle the cell. Choose deliberately and document unusual choices in a nearby note or column heading.

IFERROR versus IFNA

IFNA is a narrower alternative: it replaces only the #N/A error. IFERROR replaces any error value it encounters. This difference is important in lookup formulas, because a missing match commonly produces #N/A, while other errors may signal a mistake in the formula or source data.

Using the same layout as the lookup example—search value in B2 and lookup table in D2:E100—=IFNA(VLOOKUP(B2,$D$2:$E$100,2,FALSE),"Not found") returns “Not found” for a missing match but does not hide a different error, such as an invalid reference. That can make it easier to discover a problem that should be corrected rather than covered up.

For Microsoft 365 and newer Excel versions, XLOOKUP has an optional argument for a value to return when no match is found. That can be simpler than wrapping XLOOKUP in an error handler when missing matches are the only condition you want to handle. IFERROR remains useful when the expression may produce several kinds of errors and you intentionally want to replace all of them.

Common mistakes and troubleshooting

The most frequent mistake is using IFERROR to hide every error without checking what caused it. A workbook may look cleaner while containing misspelled function names, deleted references, bad input, or unexpected zeros. During development, consider leaving errors visible until the calculation is confirmed. Then add handling for errors that are expected and define a meaningful fallback.

Expecting it to catch a blank: A blank cell is not itself an error. Some operations treat a blank as zero or as empty text, so IFERROR may return the calculation rather than the fallback.
Expecting it to catch zero: A result of 0 is a valid number, not an error. IFERROR returns zero unchanged. Use a logical test if zero needs separate treatment.
Using a broad fallback for a narrow issue: If only #N/A is expected, IFNA can keep other errors visible.
Assuming a blank fallback deletes the formula: The cell still contains a formula that returns empty text. It can behave differently from a genuinely empty cell.
Overlooking the replacement’s type: Returning text for some rows and numbers for others can affect sorting, calculations, and charts.

If the result is unexpected, temporarily remove IFERROR and inspect the original error. Check the referenced cells, confirm that numbers are actually numeric, and verify that copied formulas still point to the intended ranges. Once you understand the cause, decide whether the error is an expected case to handle or a problem that should remain visible.

IFERROR with arrays and spilled results

In Microsoft 365, some formulas return arrays that spill into neighboring cells. IFERROR can be used around an array-producing calculation; errors in the result can be replaced while the returned result remains an array. The behavior can therefore affect multiple output cells rather than just one visible cell. Make sure the replacement makes sense for each affected result.

If a dynamic-array formula displays an error, first identify whether the error comes from the calculation itself or from the spill area. For example, a blocked spill range produces a spill-related error because cells needed for the output are occupied. Replacing that error with a message may hide the blockage, but it will not clear the cells or allow the array to spill. Resolve the underlying issue when the formula is supposed to return its full array.

Frequently Asked Questions

Does IFERROR return zero when a formula has an error?
Not automatically. It returns whatever you put in the second argument. If you specify zero as the fallback, it returns zero for an error; otherwise it returns your chosen replacement.

Can IFERROR handle only #N/A?
It can handle #N/A, but it also handles other error values. Use IFNA when you want to replace only #N/A and leave other errors visible.

Does IFERROR hide a formula error permanently?
No. The underlying formula is still there, and you can inspect it by editing the cell or removing the wrapper. However, the displayed fallback can make the problem less obvious, so broad use can make troubleshooting harder.

Why does IFERROR show a blank even though the cell is not empty?
A fallback written as an empty text string displays as blank, but the cell still contains a formula. Other formulas, filters, and data-handling tasks may distinguish that result from a truly empty cell.

Should I use IFERROR around every formula?
No. Use it when an error is an expected possibility and you have a sensible replacement. Leaving unexpected errors visible often makes a workbook easier to audit and maintain.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.