IFERROR
ALS.FOUT in Dutch Excel
Returns the result of a calculation, or a replacement value of your choice when that calculation ends in any error.
Last updated: 13 October 2026
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Entry no. | Net amount | VAT | VAT rate |
| 2 | 25-0501 | €0.00€ 0,00 | €0.00€ 0,00 | No base=IFERROR(C2 / B2, "No base")=IFERROR(C2 / B2; "No base") |
| 3 | 25-0502 | €12,500.00€ 12.500,00 | €2,625.00€ 2.625,00 | |
| 4 | 25-0503 | €8,000.00€ 8.000,00 | €720.00€ 720,00 | |
| 5 | 25-0504 | €3,400.00€ 3.400,00 | €714.00€ 714,00 | |
| 6 | 25-0505 | €1,250.00€ 1.250,00 | €112.50€ 112,50 |
Swipe sideways to see all columns
Result: No base
=IFERROR(C2 / B2,"No base")
=IFERROR(C2 / B2;"No base")
What IFERROR does and when to use it
What it does: it returns the result of a calculation, or your own value when that calculation ends in an error, for example your own text instead of #DIV/0! for a ratio on a row with no revenue.
When to use it: where an error is an expected, harmless outcome, such as a ratio on a row without a base or imported text that is sometimes not a number. It replaces the older IF(ISERROR(x), alternative, x).
Two main gains:
- Calculated once: the calculation is written once, not twice as in IF(ISERROR(x), alternative, x).
- One function: a single function replaces the IF and ISERROR pair, so the formula is shorter to read.
How Excel evaluates it
Excel calculates value first. If the outcome is not an error, IFERROR returns it and value_if_error is never calculated. If the outcome is any error, IFERROR throws it away and calculates value_if_error instead. It does not look at which error it was.
When value produces an array, the check happens per element, so one bad row is replaced while the others keep their results.
Watch out: IFERROR hides every error, including a broken range or a wrong column number. For lookups use IFNA, which replaces only #N/A (not found).
Mental model
A receptionist who answers every call that goes unanswered upstairs with the same polite message, whether the caller had the wrong number or the building is on fire.
Terms used on this page: error value, array
IFERROR(value,value_if_error)
IFERROR(value;value_if_error)
| Argument | Type | Meaning |
|---|---|---|
value | Required | The calculation to try; its result is returned when it is not an error. |
value_if_error | Required | What to show instead when the calculation returns any error. |
Return value:value, or value_if_error when value is an error
Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).
Worked examples
Beginner
VAT rate per entry without #DIV/0!
You recalculate the VAT rate on sales entries from the XAF auditfile by dividing the VAT amount by the net amount. Entry 25-0501 is a zero-value correction with 0 net and 0 VAT, so its row divides 0 by 0 and shows #DIV/0!.
You want that row to say No base, so the other rates stay readable against 21% and 9%.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Entry no. | Net amount | VAT | VAT rate | |
| 2 | 25-0501 | €0.00€ 0,00 | €0.00€ 0,00 | No base=IFERROR(C2 / B2, "No base")=IFERROR(C2 / B2; "No base") | |
| 3 | 25-0502 | €12,500.00€ 12.500,00 | €2,625.00€ 2.625,00 | ||
| 4 | 25-0503 | €8,000.00€ 8.000,00 | €720.00€ 720,00 | ||
| 5 | 25-0504 | €3,400.00€ 3.400,00 | €714.00€ 714,00 | ||
| 6 | 25-0505 | €1,250.00€ 1.250,00 | €112.50€ 112,50 | ||
| 7 |
Swipe sideways to see all columns
Formula in D2
=IFERROR(C2 / B2,"No base")
=IFERROR(C2 / B2;"No base")
Result: No base
Step by step
- Excel calculates value first:
C2/B2is 0 / 0. Dividing by zero is not possible, so the outcome is #DIV/0!. - IFERROR checks the outcome, sees an error, and discards it.
- It returns value_if_error instead: the text No base.
- Copied to
D3, value is 2625 / 12500 = 0.21. That is not an error, so IFERROR passes 0.21 (21%) through untouched and No base is never used. - The other rows work the same way: 720 / 8000 = 0.09, 714 / 3400 = 0.21 and 112.5 / 1250 = 0.09. Only the row with a base of 0 shows No base; every other row shows its real rate.
Watch out: If B2 held the text 1250 EUR (an amount that came through the export as text) instead of 0, the division would return #VALUE! and IFERROR would still show No base. The label is then wrong: the problem is a text amount, not a zero-value entry.
More on this example: why it works
Why it works
IFERROR only steps in after the division has been tried. A working division reaches the cell unchanged, and only the failed one is swapped for the text.
Intermediate
Account mapping that flags accounts missing from the list
You map ledger accounts to financial statement lines with the client's mapping table in A:B. A few accounts used in the trial balance are not in the table yet. The formula is copied down next to every account in column D, and a missing account should read Not mapped, so you know which accounts to ask the client about.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Account | Statement line | Account | Statement line | ||
| 2 | 11001100 | Cash and bank | 45004500 | Not mapped=IFERROR(VLOOKUP(D2, $A$2:$B$6, 2, FALSE), "Not mapped")=IFERROR(VLOOKUP(D2; $A$2:$B$6; 2; FALSE); "Not mapped") | ||
| 3 | 13001300 | Trade receivables | 80008000 | |||
| 4 | 16001600 | Trade payables | 13001300 | |||
| 5 | 40004000 | Personnel costs | ||||
| 6 | 80008000 | Revenue | ||||
| 7 |
Swipe sideways to see all columns
Formula in E2
=IFERROR(VLOOKUP(D2,$A$2:$B$6, 2, FALSE), "Not mapped")
=IFERROR(VLOOKUP(D2;$A$2:$B$6; 2; FALSE); "Not mapped")
Result: Not mapped
Step by step
- Excel evaluates the inner VLOOKUP first. It looks for
D2, account 4500, in the first column of$A$2:$B$6, with FALSE meaning exact match only. - It checks 1100, 1300, 1600, 4000, 8000: none is 4500. VLOOKUP returns #N/A.
- IFERROR receives #N/A as its value, recognizes an error and returns value_if_error: Not mapped. Without IFERROR,
E2would show #N/A. - In
E3the account is 8000. VLOOKUP finds it in the fifth row of the table and returns the second column of that row,B6: Revenue. No error, so IFERROR passes it on. E4finds 1300 in the second row and returnsB3: Trade receivables. The $ signs keep the table atA2:B6in every copy; without themE4would searchA4:B8and miss account 1300.
Watch out: Change the 2 into 3 by accident and VLOOKUP returns #REF! on every row, because the table has only two columns. IFERROR turns all of them into Not mapped, and you start asking the client about accounts that are mapped already.
More on this example: why it works, 1 variation
Why it works
A missing account makes VLOOKUP return #N/A, which is an error IFERROR can catch, so the only thing you see is the message. Mapped accounts produce a statement line, and IFERROR leaves that alone.
Variations
=IFNA(VLOOKUP(D2,$A$2:$B$6, 2, FALSE), "Not mapped")
=IFNA(VLOOKUP(D2;$A$2:$B$6; 2; FALSE); "Not mapped")
IFNA replaces only #N/A, so a wrong column number still shows #REF!. This is the safer choice for any lookup.
Advanced
Average confirmation rate across debtors, skipping the one without balance
For your follow-up of debtor confirmations you want the average share of the ledger balance that debtors confirmed, in one cell. Customer D1003 has a ledger balance of 0 and no confirmation, so its ratio is #DIV/0!. The average must be taken over the four debtors with a balance, not dragged down by a fake 0%.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Relation | Ledger balance | Confirmed | Average confirmed | ||
| 2 | D1001 | €48,000.00€ 48.000,00 | €45,600.00€ 45.600,00 | 91.25%91,25%=AVERAGE(IFERROR(C2:C6 / B2:B6, ""))=AVERAGE(IFERROR(C2:C6 / B2:B6; "")) | ||
| 3 | D1002 | €36,000.00€ 36.000,00 | €36,000.00€ 36.000,00 | |||
| 4 | D1003 | €0.00€ 0,00 | €0.00€ 0,00 | |||
| 5 | D1004 | €25,000.00€ 25.000,00 | €20,000.00€ 20.000,00 | |||
| 6 | D1005 | €18,000.00€ 18.000,00 | €16,200.00€ 16.200,00 | |||
| 7 |
Swipe sideways to see all columns
Formula in E2
=AVERAGE(IFERROR(C2:C6 / B2:B6,""))
=AVERAGE(IFERROR(C2:C6 / B2:B6;""))
Result: 91.25%91,25%
Step by step
- Excel starts inside.
C2:C6/B2:B6divides row by row: 45600 / 48000 = 0.95, 36000 / 36000 = 1, 0 / 0 = #DIV/0!, 20000 / 25000 = 0.8 and 16200 / 18000 = 0.9. - IFERROR checks each of the five elements. Only the third is an error, so only that one becomes empty text: 0.95, 1, "", 0.8, 0.9.
- AVERAGE ignores text inside an array, so it adds four numbers and divides by four: (0.95 + 1 + 0.8 + 0.9) / 4 = 3.65 / 4 = 0.9125, shown as 91.25%.
- Compare value_if_error 0: AVERAGE would get five numbers and return 3.65 / 5 = 0.73, or 73%. No error appears; the figure is quietly lower than the truth and suggests a confirmation problem that does not exist.
- If D1003 later gets a ledger balance of 20000 and a confirmed 19000, its ratio is 0.95 and the result becomes (3.65 + 0.95) / 5 = 0.92, or 92%.
Watch out: If a balance cell holds the text n/a, the division gives #VALUE! and IFERROR removes that debtor from the average too, without any sign. Four debtors are averaged while you think it is five.
More on this example: why it works, 2 more pitfalls, 2 variations
Why it works
IFERROR works element by element on an array, so it removes the one failing row without touching the others. Empty text is the right replacement because AVERAGE skips text in arrays, while a 0 would count as a real debtor that confirmed nothing.
More to watch out for
- In Excel 2019 and earlier, AVERAGE(IFERROR(C2:C6 / B2:B6, "")) and the IF variation below must be confirmed with Ctrl+Shift+Enter, otherwise the division is not calculated per row. Excel 2021 and Microsoft 365 need no special key.
- This is an average of percentages, so a small debtor counts as heavily as a large one. In amounts, 117800 of 127000 is confirmed, about 92.8%, which answers a different question.
Variations
=AVERAGE(IF(B2:B6 <> 0,C2:C6 / B2:B6, ""))
=AVERAGE(IF(B2:B6 <> 0;C2:C6 / B2:B6; ""))
Tests the cause (zero balance) instead of catching the error. Prefer it: a text value in the data still shows as #VALUE! instead of vanishing.
=SUM(C2:C6) / SUM(B2:B6)
=SUM(C2:C6) / SUM(B2:B6)
The weighted confirmation rate over all amounts, about 92.8%. Use it when the question is how much of the balance is covered, not how the typical debtor responded.
MasterExcel 2021 or later (This example needs Excel 2021 or later)
Catch only the division error, let bad data show
In the analytical review of the trial balance you compute the change of each cost account against last year. An account used for the first time this year has 0 last year and should read New. One row came from the client's export with the text n/a instead of a number, and that row must stay visibly broken so someone fixes the source, instead of being labelled New as well.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Account | Prior year | This year | Change | |
| 2 | Advisory fees | €0.00€ 0,00 | €8,400.00€ 8.400,00 | New=LET( growth, C2 / B2 - 1, IFERROR(growth, IF(ERROR.TYPE(growth) = 2, "New", growth)) )=LET( growth; C2 / B2 - 1; IFERROR(growth; IF(ERROR.TYPE(growth) = 2; "New"; growth)) ) | |
| 3 | Rent | €12,000.00€ 12.000,00 | €13,800.00€ 13.800,00 | ||
| 4 | Travel | n/a | €5,200.00€ 5.200,00 | ||
| 5 | Wages | €9,500.00€ 9.500,00 | €7,600.00€ 7.600,00 | ||
| 6 | Insurance | €4,000.00€ 4.000,00 | €4,000.00€ 4.000,00 | ||
| 7 |
Swipe sideways to see all columns
Formula in D2
=LET( growth,C2 / B2 - 1, IFERROR(growth, IF(ERROR.TYPE(growth) = 2, "New", growth)) )
=LET( growth;C2 / B2 - 1; IFERROR(growth; IF(ERROR.TYPE(growth) = 2; "New"; growth)) )
Result: New
Step by step
- LET calculates growth once:
C2/B2- 1 is 8400 / 0 - 1. The division fails with #DIV/0!, and subtracting 1 from an error keeps the error. - IFERROR sees an error in growth and turns to value_if_error, which is not a fixed text but a second decision.
- ERROR.TYPE(growth) returns the error's code number. #DIV/0! is code 2, so 2 = 2 is TRUE and the IF returns New.
- Copied to
D3: 13800 / 12000 - 1 = 1.15 - 1 = 0.15. No error, so IFERROR returns 15% and the second decision is never calculated. D4divides 5200 by the textn/aand gets #VALUE!, code 3. 3 = 2 is FALSE, so the IF returns growth itself, which is the #VALUE! error. The broken row stays broken.D5gives 7600 / 9500 - 1 = 0.8 - 1 = -0.2, or -20%, andD6gives 4000 / 4000 - 1 = 0, or 0%. A plain IFERROR(growth, "New") would have labelled the travel account New as well.
Watch out: The mirror-image mistake is IFERROR(C2 / B2 - 1, 0). Both the new account and the broken export then show 0%, which reads as unchanged, the one outcome that nobody questions in an analytical review.
More on this example: why it works, 1 more pitfall, 1 variation
Why it works
IFERROR decides whether there is an error, and ERROR.TYPE decides which one: 2 for #DIV/0!, 3 for #VALUE!, 7 for #N/A. Returning growth for any other code hands the original error back, so only the expected case is translated. LET keeps the division in one place, which means the formula calculates it once and cannot drift apart when edited.
Do not use this construction when there is one known cause you can test beforehand: IF(B2 = 0, "New", C2 / B2 - 1) is shorter and does the same here. ERROR.TYPE earns its place when the error comes out of a deeper chain you cannot test up front; for lookups, IFNA alone does the job.
More to watch out for
- ERROR.TYPE on a value that is not an error returns #N/A. That is why it sits inside value_if_error, where growth is guaranteed to be an error.
Variations
=IF(B2 = 0,"New", C2 / B2 - 1)
=IF(B2 = 0;"New"; C2 / B2 - 1)
Tests the cause directly. The text n/a is not 0, so D4 still shows #VALUE!. Prefer this when the only expected failure is a zero you can see coming.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| Not found on every row of a lookup | A lookup wrapped in IFERROR shows Not found on every row after someone changed the column number or deleted a column. | The real error was #REF!, and IFERROR hid it. Switch these lookups to IFNA. |
| The average of ratios drops | An average of ratios drops after you replaced #DIV/0! with 0. | A 0 is a value and counts in the average. Return "" instead; AVERAGE ignores text. |
| The total shrinks without any error | A whole model sits inside one IFERROR(..., 0), and after a sheet was deleted the total quietly became smaller. | Wrap only the step that can legitimately fail. Errors anywhere else are messages you need to see. |
Used in combinations
- Find the customer from a bank descriptionCut the invoice number out of a bank statement description with TEXTAFTER and TEXTBEFORE, then look up the customer in the receivables list with XLOOKUP.Excel 2024 or later (This combination needs Excel 2024 or later)