Approximate match: bands and grades

1 / 5

Give each student a grade from the boundaries in E2:F7. Most scores, like 64, aren't in the table at all.

keyboard Typing formulas is easiest on a computer with a keyboard.

boundaries

scores

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How VLOOKUP and XLOOKUP works

What is an approximate match?

An exact match only finds values that are in the table. Bands work differently: a score of 67 isn't in a grade table, but it falls between the 60 and 70 boundaries, so it gets the grade for 60. An approximate match finds the largest value that is less than or equal to what you look up.
=VLOOKUP(B2,$E$2:$F$7,2,TRUE)
How should the band table look?
- One row per band, with the lowest value of each band in the first column: 0, 40, 50...
- Sorted from smallest to largest. On unsorted data an approximate match quietly returns wrong answers.
- Start at the lowest possible value (0 here). A score below the first band gives #N/A.
Why TRUE?

TRUE asks for an approximate match. VLOOKUP also does one when the last part is left out, which is why forgetting FALSE in an ordinary lookup gives wrong answers. Write TRUE so the next reader knows it is on purpose.
What else uses bands?

Tax rates, commission levels, shipping prices by weight and discounts by order size. In XLOOKUP, use -1 as the match mode for "exact match or next smaller": =XLOOKUP(B2,$E$2:$E$7,$F$2:$F$7,,-1).

Know someone fighting with a spreadsheet? Send them this page.