Course outline / Lookup functions
Lesson 29 of 75 · Advanced · 3 exercises
Approximate lookups for bands
Find which band a value falls into, such as a grade or a tax rate.
What you'll practise
- Find the band with VLOOKUP TRUE
- The same with LOOKUP
- Which band number?
Find the band with VLOOKUP TRUE
Not every lookup wants an exact match. For bands, such as grades or price breaks, list the lowest value of each band in ascending order, and end VLOOKUP with TRUE.
VLOOKUP then finds the last band that is not larger than your value.
In cell F2, look up the score in D2 in A2:B5 and return the grade, using TRUE.
The same with LOOKUP
LOOKUP does an approximate match with two ranges: the band starts and the results.
=LOOKUP(value, band_starts, results)
In cell F2, use LOOKUP to find the grade for D2, with starts A2:A5 and grades B2:B5.
Which band number?
MATCH with 1 as its last argument finds the position of the band too.
In cell F2, use MATCH to find which band, 1 to 4, the score in D2 falls into in A2:A5.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.