QUICK TABLE
Four errors, four first checks
| Error | Usual cause | First check |
|---|---|---|
| #REF! | A formula points to a cell or range that was deleted or moved beyond repair. | Select the formula and inspect every reference, especially after deleting a row or column. |
| #VALUE! | An argument has the wrong type, such as text where arithmetic expects a number. | Use ISNUMBER or inspect hidden spaces and text-formatted numerals. |
| #N/A | A lookup did not find an acceptable match. | Check exact spelling, spaces, data types, and whether the lookup range is correct. |
| #DIV/0! | The denominator is zero or blank. | Decide whether a zero denominator means blank, zero, or a data-quality exception. |
FALLBACK
Do not use IFERROR as a blindfold
IFERROR is useful when a known miss has a clear meaning. It is risky when it turns every unexpected problem into an empty cell.
Explicit fallback for a missing lookup
=IFERROR(XLOOKUP(E2,A:A,C:C),"Not found")“Not found” is more informative than an empty string because it distinguishes a lookup miss from a genuinely blank result.
DIVIDE
Handle zero denominators deliberately
Decide the business meaning before choosing a result. A blank denominator may mean “not measured,” while a true zero denominator may make the ratio undefined.
Name the zero case instead of hiding it
=IF(B2=0,"Not applicable",A2/B2)WORKFLOW
A five-minute debug order
- 1. Reproduce the error in one cell; do not edit the entire column at once.
- 2. Inspect highlighted references and confirm the expected cell types.
- 3. Check spaces, text-formatted numbers, and exact-match settings.
- 4. Evaluate nested functions from the inside out.
- 5. Add IFERROR only when the fallback has a defined meaning.