Spreadsheet Practice

Lesson 12 of 29, Lookups

Tiered lookups & IFERROR

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

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.

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 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Commission tiers
  2. Unknown SKUs
Try these functions in the Formula Lab