# Tiered lookups & IFERROR (Excel lesson 15 of 45)

> Commission tiers, tax brackets and shipping bands are approximate lookups.

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

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Lookups"
- **Time:** about 9 minutes, 2 graded exercises
- **Functions:** `XLOOKUP`, `VLOOKUP`, `IFERROR`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)`: Exact or next smaller

## How it works

Some lookups aren't exact. With thresholds sorted smallest to largest, ask for the "exact or next smaller" match: XLOOKUP with match_mode `-1`, or VLOOKUP with `TRUE`.

`IFERROR(value, fallback)` swaps any error for something readable. Use it for expected gaps, like an unknown SKU, not to hide formulas that are wrong.

## Syntax

| Formula | What it does |
|---|---|
| `=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)` | Exact or next smaller |
| `=VLOOKUP(B2,$E$2:$F$5,2,TRUE)` | Classic approximate match |
| `=IFERROR(VLOOKUP(A2,$E$2:$F$5,2,FALSE),"Check SKU")` | Friendly fallback |

## Watch out for

- Approximate match needs the thresholds sorted ascending.
- IFERROR also hides typos in your formula. Test before you wrap.

## The exercises

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

1. **Commission tiers.** In C2, look up each rep's commission rate from the tier table in E2:F5. Sales at or above a threshold earn that tier. Fill down to C7.
2. **Unknown SKUs.** In C2, look up the unit price for each SKU from E2:F5. If the SKU isn't in the price list, show Check SKU. Fill down to C6.

Previous: [INDEX & MATCH](https://spreadsheetpractice.com/learn/index-match) · Next: [Lookups with several criteria](https://spreadsheetpractice.com/learn/multi-criteria-lookups)

---

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