IFERROR is an Excel function that checks a formula and, if the formula returns an error, gives you another value instead. It turns ugly results such as #DIV/0! and #N/A into a clean zero, a blank cell or a short message such as Not found. It is one of the most used functions in real workbooks, because dashboards and reports look unprofessional when they are full of errors. But IFERROR can also hide real problems if you use it carelessly, so it is important to know when to use it and when to use something more specific. This guide shows the syntax, worked examples with numbers you can check, the difference between IFERROR and IFNA, and the mistakes to avoid.
Updated November 2026
In this guide
- What IFERROR does
- The common Excel errors
- Example 1: division by zero
- Example 2: a lookup that does not find a match
- Example 3: averages and percentages
- Example 4: sum of values that may contain errors
- IFERROR versus IFNA
- Return a different calculation
- Format the cell instead
- When not to use IFERROR
- A better check for division
- A report example
- Common mistakes
- Practice problems
- Quick recap
What IFERROR does
IFERROR has two arguments. The syntax is IFERROR(value, value_if_error). Excel calculates the first argument. If it is a normal result, IFERROR returns it. If it is any of the Excel errors, IFERROR returns the second argument instead. The errors it catches are #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!. The function is available in Excel 2007 and later, so it works in all current versions. It is a simpler alternative to the older pattern of IF with ISERROR, which needed the formula twice.
The common Excel errors
Each error has its own meaning.
| Error | Typical cause |
|---|---|
| #DIV/0! | Division by zero or by an empty cell |
| #N/A | A lookup value was not found |
| #VALUE! | Wrong type of value, such as text in a calculation |
| #REF! | A reference to a deleted cell or sheet |
| #NAME? | A misspelled function or name |
| #NUM! | An invalid number in a calculation |
Understanding the cause helps you decide whether to hide the error or fix the formula.
Example 1: division by zero
Suppose column B holds sales and column C holds the number of orders, and you want the average order value with =B2/C2. If sales are 500 and orders are 4, the result is 125. If a new product has 0 orders, the formula returns #DIV/0!. Wrap it: =IFERROR(B2/C2,0). For 500 and 4 you still get 125, and for 0 orders you get 0. You can also return an empty text with =IFERROR(B2/C2,""), which shows a blank cell. A blank looks cleaner, but a zero can be used in later calculations.
Example 2: a lookup that does not find a match
With a price list, =VLOOKUP(E2,A:B,2,FALSE) returns #N/A when the item in E2 is not in the list. Use =IFERROR(VLOOKUP(E2,A:B,2,FALSE),"Not found"). If the item exists and has a price of 12, the result is 12. If not, the cell shows Not found. The same pattern works with XLOOKUP, although XLOOKUP has its own fourth argument for not found, so you often do not need IFERROR there. See our XLOOKUP guide and our VLOOKUP not working guide for more.
Example 3: averages and percentages
A percentage change formula such as =(C2-B2)/B2 returns #DIV/0! when B2 is zero. For last year sales of 200 and this year 250, the result is 0.25, or 25 percent. For last year sales of 0, the formula fails. Use =IFERROR((C2-B2)/B2,"n/a"). A text such as n/a tells the reader that the change cannot be calculated, which is more honest than showing zero. Our percent change guide explains the formula in more detail.
Example 4: sum of values that may contain errors
If a column has some error cells, SUM returns an error. For example, the values 10, 20 and #N/A give an error as the total. To ignore the errors, use IFERROR inside an array formula, =SUM(IFERROR(A2:A4,0)), which treats each error as 0 and returns 30. In older Excel versions, you must enter it with Ctrl+Shift+Enter, and in Excel 365 it works normally. Another option is the AGGREGATE function, which can ignore errors. It is better, however, to fix the source of the errors than to hide them in the total.
IFERROR versus IFNA
IFNA works like IFERROR but catches only the #N/A error. That is useful for lookups, because #N/A means not found, while other errors such as #REF! or #NAME? mean that something is wrong with the formula. If you use IFNA, those other errors stay visible, so you will notice them and fix them. For example, =IFNA(VLOOKUP(E2,A:B,2,FALSE),"Not found") shows Not found for missing items, and still shows #REF! if the lookup range was deleted. IFNA is available in Excel 2013 and later.
Return a different calculation
The second argument does not have to be a fixed value. It can be another formula. For example, =IFERROR(VLOOKUP(E2,Table1,2,FALSE),VLOOKUP(E2,Table2,2,FALSE)) looks in the first table and, if the item is missing, looks in the second. You can chain this to search three tables. This is a handy way to combine two price lists. If the item is not in either list, the second lookup returns #N/A, so add a final text with another IFERROR around the whole formula.
Format the cell instead
In some cases, you can leave the error and hide it with formatting. Conditional formatting can color the font white when the cell has an error, with the rule =ISERROR(A1). This hides the text without changing the formula. However, hidden errors can still break other formulas that use the cell, so use this only for the final layout of a report. For printing, you can also choose to show cell errors as blank in Page Layout, Sheet Options. Our conditional formatting guide explains rules like this.
When not to use IFERROR
Do not use IFERROR as a habit on every formula. If a formula has an error because of a mistake, such as a misspelled function name or a deleted column, IFERROR will hide the mistake, and the sheet will show a wrong value like 0 with no warning. First find out why the error occurs. Use IFERROR only when the error is expected and has a clear meaning, such as a missing lookup or a zero denominator. When you use it, choose a message that tells the reader what happened. Silent zeros can cause wrong decisions.
A better check for division
For division, an explicit check is often clearer. =IF(C2=0,0,B2/C2) tests the denominator and avoids the error directly. This formula does not hide other errors, such as a text value in B2, which would still show #VALUE!. That is an advantage, since you will see data problems. Use IF for division checks when you want to be strict, and IFERROR when you want a simple, short formula and you know the data is clean. Our IF statements guide explains the pattern.
A report example
Imagine a sales report with the columns Product, Units, Revenue and Price per unit. The price per unit is revenue divided by units, so for 3 products with revenue 600, 450 and 0, and units 30, 15 and 0, the prices are 20, 30 and an error for the last one. With =IFERROR(C2/B2,"-"), the report shows 20, 30 and a dash. The dash tells the reader that nothing was sold, and the column still lines up neatly. If you then average the price column, AVERAGE ignores text, so the average is 25, which is the average of 20 and 30. If you had returned 0 instead, the average would be 16.67, which would be misleading.
Common mistakes
The most common mistake is to wrap a long formula in IFERROR and never look at it again, so real problems stay hidden. Another is to return zero when blank or text would be clearer. A third is to forget the quotes around a text message. A fourth is to use IFERROR with a formula that does not return errors for the case you wanted, such as a lookup that returns the wrong match without an error. A fifth is to nest too many IFERROR functions, making the formula hard to read. Keep the formulas simple and test them with good and bad data.
Practice problems
Try these. Problem 1: =IFERROR(10/2,0) returns 5. Problem 2: =IFERROR(10/0,0) returns 0. Problem 3: =IFERROR("a"+1,"Check") returns Check, because adding text to a number gives #VALUE!. Problem 4: write a formula that returns Not found when a lookup fails. Problem 5: compare =IFERROR(A1/B1,0) with =IF(B1=0,0,A1/B1) for A1 equal to 5 and B1 equal to 0, and say what each shows. Both return 0, but only the IF version would show an error for text in A1.
Quick recap
IFERROR(value, value_if_error) returns the value when there is no error and the second argument when there is one. Use it for expected errors such as division by zero and lookups that find nothing. Return a clear message or a blank, use IFNA when only #N/A should be hidden and use IF checks when you want to keep other errors visible. Do not use IFERROR to cover up broken formulas. With sensible use, your reports stay clean and your results stay trustworthy.
Frequently asked questions
What does IFERROR do in Excel?
It returns a value you choose when a formula results in an error.
What errors does IFERROR catch?
All errors, including #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, #NUM! and #NULL!.
What is the difference between IFERROR and IFNA?
IFNA catches only #N/A.
Can I return a blank with IFERROR?
Yes, use two double quotes as the second argument.
Does IFERROR work in all Excel versions?
It works in Excel 2007 and later.


