IFERROR
Show something friendlier when a formula cannot answer.
Show something readable when a formula cannot answer
The formula's own result, or your fallback if it errored
Syntax
=IFERROR(value, value_if_error)
| Argument | Required | What goes here |
|---|---|---|
value | Required | The formula to run, usually a lookup that might not find a match. |
value_if_error | Required | What to show instead of an error, like "Not on the chart of accounts". |
How to use IFERROR
IFERROR runs the formula you give it and hands back the result. If the formula errors, it hands back your second argument instead.
Its proper use is an error you have already understood and decided what to do about. A supplier that is genuinely not on the terms table gets the standard thirty days, and saying so in the formula is better than a column of errors.
Its improper use is much more common: wrapping a formula you have not checked, so a wrong column number or a mistyped range quietly returns the fallback for every row and the schedule looks finished.
Write the lookup first. Get it right on a row you can check by eye. Only then wrap it.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=IFERROR(VLOOKUP("Harbour Ltd",G2:H6,2,FALSE),"Not on file")Harbour Ltd is not in the terms table, so the fallback answers. | Not on file |
=IFERROR(VLOOKUP(B4,G2:H6,2,FALSE),"Not on file")Meridian Freight is on file, so IFERROR does nothing at all and the real answer comes through. | 45 |
=IFERROR(E2/D5,"No days to spread over")Row 5 is nought days old, so the division fails and the message answers. | No days to spread over |
=IFERROR(VLOOKUP(B4,G2:H6,3,FALSE),"Not on file")The supplier is on file and the column number is wrong. IFERROR reports it as not on file, which is the trap. | Not on file |
Notes
- IFERROR catches every kind of error, including the ones caused by your own formula. That is exactly why it hides mistakes.
- A fallback of 0 in an amount column is worse than an error, because a total will accept it without complaint.
Related
A reference tells you what it does. A unit makes you use it.