Challenge 2: A Tiered Commission Rate Lookup — Solution Walkthrough

Setup: A2:A5 = 0, 2000, 8000, 15000 (thresholds, sorted ascending).
B2:B5 = 2%, 4%, 6%, 8%. D1 = 9500.

Formula:
  =XLOOKUP(D1, A2:A5, B2:B5, "No match", -1)

Result: 6%

Walkthrough:

9500 does not exactly equal any of the four threshold values (0,
2000, 8000, 15000) — there is no exact match to find. Match mode -1
tells XLOOKUP: "if there's no exact match, use the largest value in
the lookup array that is still less than or equal to the search
value." Scanning A2:A5, the largest threshold that doesn't exceed
9500 is 8000 (15000 is too big). XLOOKUP therefore returns whatever
sits in the SAME row as 8000 in B2:B5, which is 6%.

Why match mode -1 is required, not the default 0 (exact match):

With match mode 0 (or omitted, since 0 is XLOOKUP's default), the
function would search A2:A5 for a value that EQUALS 9500 exactly —
find nothing, since 9500 isn't one of the four listed thresholds —
and return the fallback text, "No match", instead of a real
commission rate. Real-world tiered data almost never has a threshold
that exactly matches every possible search value; the whole point of
a tier system is that a RANGE of values (here, "8000 up to just
under 15000") all resolve to the same tier. Match mode -1 is what
lets one small reference table cover every value in between its
listed thresholds, not just the exact threshold values themselves.

Notes:
- This exact formula shape is what this chapter frames as the
  cleaner alternative to a long nested IF chain (Excel Fundamentals
  Chapter 4) — adding a 5th commission tier here means adding one row
  to A2:B2, not touching the formula's own logic at all.