IF tests a condition and returns one result when it is true and another when it is false; IFERROR evaluates a formula and substitutes a chosen result only when that formula produces an error. Use IF to make decisions based on data, and use IFERROR to handle errors that would otherwise appear in a result cell.
What IF and IFERROR do
Both functions let a worksheet show a useful result instead of simply displaying a raw calculation, but they respond to different things. IF responds to a logical test: a comparison or other expression that Excel can evaluate as true or false. For example, a test might ask whether a quantity is zero, a score meets a threshold, or a cell is blank.
IFERROR responds to the result of a calculation. If the calculation produces an Excel error, the function returns the fallback value you specify. If the calculation succeeds, it returns the calculation’s ordinary result. It does not decide whether a value is reasonable or meets a business rule; it only checks whether the result is an error.
The distinction matters because a calculation can be valid but still unsuitable for a report. A negative balance, a low score, or a zero may be perfectly valid numbers, not errors. Conversely, a division by zero is an error even if the formula is otherwise logically appropriate.
How each function works
IF has three arguments: a condition to test, the value to return if that condition is true, and an optional value to return if it is false. If the false result is omitted, Excel returns FALSE when the condition is false. The true and false results can be text, numbers, references, or further formulas.
IFERROR takes two arguments: the value or expression to evaluate, and the value to return if the first argument produces an error. The fallback can be a message, a number, a blank text value, or another calculation. If the first argument does not produce an error, Excel returns its result unchanged.
In practical terms, Excel evaluates the test inside IF and chooses a branch. With IFERROR, it evaluates the supplied expression and checks whether that evaluation resulted in an error. Neither function automatically corrects a bad source value. A fallback can make a sheet easier to read, but the underlying problem may still need attention.
Example: use IF for a decision
Suppose a class’s scores are in column C, with one score in C2, and a score of 70 or higher counts as a pass. The cell layout is a score in C2; enter this formula in the result cell: =IF(C2>=70,"Pass","Review"). If C2 contains 82, the result is Pass. If it contains 64, the result is Review.
This is a suitable use of IF because the formula expresses a rule about the data. The result does not depend on an error: both 82 and 64 are valid numbers, and the test decides which label to return. If C2 contains text that cannot be compared as intended, the result may not match the policy the author meant to apply. It is worth validating the input data rather than assuming every cell contains a score.
Another common decision is preventing division by zero. Suppose A2 contains revenue and B2 contains units sold. If B2 is zero, an average price per unit is undefined for that row. The cell layout is revenue in A2 and units in B2; use: =IF(B2=0,"No units",A2/B2). If B2 is zero, Excel returns No units without attempting to show a division-by-zero result; if B2 is 5 and A2 is 100, the formula returns 20.
Example: use IFERROR to handle a calculation error
For the same revenue and units layout, an alternative is to let Excel attempt the division and provide a fallback for an error. With revenue in A2 and units in B2, enter: =IFERROR(A2/B2,"Check inputs"). If A2 is 100 and B2 is 5, the result is 20; if B2 is zero, the result is Check inputs rather than #DIV/0!.
That formula is shorter, but it handles a broader range of problems than the specific zero-units rule. If A2 or B2 contains a value that makes the division produce another error, the same message appears. That may be convenient in a presentation-oriented report, but it can conceal which problem occurred. Choose the fallback wording with that trade-off in mind.
A common lookup example is replacing the #N/A result produced when a key is not found. Suppose product IDs are in A2:A10, corresponding descriptions are in B2:B10, and the ID to find is in E2. Enter this formula in the result cell: =IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not found"). If E2 matches an ID in column A, Excel returns the corresponding description from column B; if there is no match, it returns Not found.
What errors IFERROR catches—and what it does not
IFERROR catches errors returned by its first argument. These can include familiar errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM!, and #NULL!. In versions with newer calculation features, formulas can also produce newer error types, such as #SPILL! or #CALC!; IFERROR is intended to return its fallback when the expression evaluates to an error.
It does not treat every unusual value as an error. Zero, an empty cell, a text string, and a negative number are not automatically errors. For instance, a lookup that successfully finds a cell containing zero has succeeded; IFERROR returns zero, not the fallback. A formula can also return an empty string without an error, and that result passes through unchanged.
This is why IFERROR is not a general-purpose data-quality test. If a cell contains a text label where a number is expected but the formula handles that text without error, the fallback will not appear. If you need to test for a particular condition—such as a blank input or a zero denominator—an explicit IF test usually communicates the rule more clearly.
How to choose the right function
Start by asking what should trigger the alternate result. If the trigger is a known condition in the data, use IF. If the trigger is any error arising from an expression, use IFERROR. The functions are not interchangeable, even though both can make a worksheet display a message instead of an unwanted result.
- Use
IFfor rules such as “if the status is Closed, show 0; otherwise show the balance.” - Use
IFto handle a known edge case, such as checking for zero units before calculating a per-unit amount. - Use
IFERRORwhen several possible formula errors can reasonably share one fallback, such as a missing lookup result in a report. - Keep an error visible when it signals a problem someone should investigate, such as a broken cell reference in a critical calculation.
You can combine the functions when you have both a data rule and a need to handle calculation errors. Suppose A2 is an optional label, B2 is a numerator, and C2 is a denominator. The cell layout is the label in A2 and the values to divide in B2 and C2; use: =IF(A2="","",IFERROR(B2/C2,"Check value")). If A2 is blank, the formula returns a blank display. Otherwise it performs the division and returns Check value if that calculation errors. For B2 equal to 10 and C2 equal to 2, with a nonblank A2, the result is 5.
Common problems and misconceptions
A frequent mistake is using IFERROR to hide every problem without considering what the fallback means. For example, returning zero for every error can make a missing value look like a genuine measured zero. In a sales table, a zero sale and a failed lookup are different facts; combining them can mislead anyone who totals or charts the results.
Another misconception is that IF catches errors simply because it has a false branch. It does not automatically do so. If the condition itself evaluates to an error, or if the selected calculation produces an error, that error can still appear. To handle a known condition, test it explicitly; to handle a calculation error, use IFERROR or another suitable error-handling approach.
Also consider whether the fallback hides the source of the problem. A generic message such as Check formula may be appropriate for a user-facing sheet, while a diagnostic worksheet may be better off showing the original error. During development, leaving errors visible can help identify a misspelled function, an invalid reference, or unexpected source data. Add a fallback once you know which errors are expected and what the reader should do about them.
Finally, avoid repeating a complicated calculation in both branches of an IF unless there is a good reason. A direct condition can be clearer for a known case, but a formula that duplicates a long expression is harder to maintain. For a single expression whose errors share a fallback, IFERROR is often more readable.
Related function: IFNA
If the only error you want to handle is #N/A, Excel also provides IFNA. For example, the cell layout can be product IDs in A2:A10, descriptions in B2:B10, and the lookup ID in E2. This formula returns Not found for an unavailable match while allowing other errors to remain visible: =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"Not found"). Use it when a missing lookup is expected but other errors may reveal a separate formula issue.
Unlike IFNA, IFERROR handles many kinds of errors, not just #N/A. This narrower behavior can make IFNA preferable for lookups when you do not want an unrelated problem, such as an invalid reference, disguised as Not found. Both functions are available in current Microsoft 365 Excel for Windows.
Frequently Asked Questions
Can IFERROR replace IF in Excel?
No. IFERROR handles error results, while IF tests a condition. A valid value that fails a business rule—such as a score below a threshold—is not an error, so IFERROR will not classify it for you.
Does IFERROR hide all Excel errors?
It returns the fallback for errors produced by its first argument, including common calculation and reference errors. It does not hide ordinary values such as zero or blank text, and it does not fix the cause of an error; it changes what the formula displays instead.
Should I use IF or IFERROR to avoid #DIV/0!?
Use IF when you specifically want to test whether the denominator is zero and show a meaningful result for that case. Use IFERROR when any error from the division should lead to the same fallback. The first option makes the zero-denominator rule explicit; the second is more general.
Can IFERROR return a blank instead of an error?
Yes. Its fallback can be an empty text string, which displays as blank. Consider whether a blank could be mistaken for missing data or a real zero, though; a short message may be more informative in a worksheet that users need to audit.
What is the difference between IFERROR and IFNA?IFERROR handles a broad set of error values. IFNA handles only #N/A. For a lookup where a missing key is expected but other formula errors should stay visible, IFNA can make the distinction clearer.
Related
- Excel Conditional Formatting for Date Ranges Explained
- Excel Conditional Formatting for Upcoming Dates
- Conditional Formatting Excel Dates Before a Date Rule
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- CHAR Excel Examples: Formulas, Line Breaks, and MoreIn Excel for Microsoft 365, use CHAR(number) to return the character assigned to a numeric code, such as…
- Sort Excel by Number: Steps, Options, and Common FixesTo sort numbers in Excel, select a cell in the number column, then choose Data > Sort Smallest…
- SUMIF in Excel: Count Values or Sum Them CorrectlySUMIF adds numbers that meet a condition; it does not count matching cells. To count matches, use COUNTIF…
- Excel Formula for Tracking Hours in a Daily TimesheetFor a basic Excel timesheet, put a start time in B2 and an end time in C2, then…
- Excel Filter by Cell Value: Shortcut, Steps, and TipsIn Excel for Microsoft 365 on Windows, there is no single dedicated shortcut that immediately filters a list…
- Excel Formula to Highlight Dates Before Today in a RangeIf your dates are in A2:A100, select that range and create a formula-based conditional-formatting rule using =AND(ISNUMBER(A2),A2<TODAY()). Choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.