# Handling errors on purpose (Excel lesson 42 of 45)

> Decide which errors to catch, and say what you want shown instead.

Source: https://spreadsheetpractice.com/learn/errors-iferror-ifna · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Errors & checks"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `IFNA`, `IFERROR`, `ISNUMBER`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=IFNA(VLOOKUP(A2,$F$2:$G$4,2,FALSE),"Not listed")`: Catch only "not found"

## How it works

An error is information. `#N/A` means a lookup found nothing. `#DIV/0!` means a divisor was zero or blank. `#VALUE!` means text is being used as a number. Hiding all of them the same way throws that information away.

`IFNA(formula, text)` catches only `#N/A`, the "not found" case. A typo in a range or a broken reference still shows up. `IFERROR` catches everything, so use it when any error genuinely has the same fix, such as a margin on zero revenue.

Sometimes the best guard is a test before the calculation: `IF(ISNUMBER(C2), B2*C2, 0)` only multiplies when there is a price to multiply. It makes the rule readable and never hides a surprise.

## Syntax

| Formula | What it does |
|---|---|
| `=IFNA(VLOOKUP(A2,$F$2:$G$4,2,FALSE),"Not listed")` | Catch only "not found" |
| `=IFERROR((B2-C2)/B2,"n/a")` | Any error becomes n/a |
| `=IF(ISNUMBER(C2),B2*C2,0)` | Calculate only when it is a number |
| `=ISERROR(D2)` | TRUE if D2 is any error |

## Watch out for

- IFERROR can hide a wrong range or a deleted column and still look fine. Prefer IFNA for lookups.
- Returning "" or text from a formula makes that cell text. Anything that does arithmetic on it will error again.
- Show a message a colleague understands: "Not listed" beats 0 or a blank when it is a data problem.

## The exercises

Solve them in the live grid at [Handling errors on purpose](https://spreadsheetpractice.com/learn/errors-iferror-ifna). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Not listed.** Fill C2:C7 with each SKU's price from the list in F2:G4. When a SKU is not in the list, show Not listed, and catch only that case.
2. **Guard the total.** In D2, calculate quantity × unit price, but only when the price is a number. Otherwise show 0. Fill down to D7.
3. **Margin on zero revenue.** In D2, calculate margin as (revenue − cost) ÷ revenue. Where revenue is 0 the division fails, so show n/a instead. Fill down to D7.

Previous: [NPV & IRR](https://spreadsheetpractice.com/learn/npv-irr) · Next: [Duplicates & reconciliation](https://spreadsheetpractice.com/learn/duplicates-reconciliation-checks)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
