Spreadsheet Practice

Lesson 15 of 45, Lookups

Tiered lookups & IFERROR

Quick answer

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

Key formula
=XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1) (Exact or next smaller)
Pro9 minutes, 2 exercisesXLOOKUPVLOOKUPIFERROR

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

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

=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

  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.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. Commission tiers
  2. Unknown SKUs