Tiered lookups
8 minutes · 4 steps · free, no sign-up
Some answers depend on a band rather than an exact match: a score of 65 is a C, an order of 340 earns a 10% discount. VLOOKUP can do this if you set it to approximate match, using a small table of where each band starts.
- Set up a band table sorted from lowest to highest
- Use VLOOKUP with TRUE to find the band
- Find the position of a band with MATCH
- Pull the answer out with INDEX and MATCH
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=VLOOKUP(A2, $D$2:$E$6, 2, TRUE) approximate match
=MATCH(A2, $D$2:$D$6, 1) position of the band
=INDEX($E$2:$E$6, position) the label at that positionThe key ideasSummary: Tiered lookups
With the last argument of VLOOKUP set to TRUE, the lookup finds the largest value that is not above your number. So a table that starts at 0, 50, 60, 70 and 85 puts 65 in the band that starts at 60. The table must be sorted from lowest to highest, or the answers will be wrong without any warning.
MATCH(..., 1) does the same search but returns the position, and INDEX returns whatever is in that position of another range. Together they do the job of VLOOKUP and can look to the left, which VLOOKUP cannot.
Watch out forCommon mistakes
- An unsorted band table. Approximate lookups assume ascending order. Out-of-order bands give wrong answers and no error.
- Leaving out the 0 row. If the first band starts above a value, you get
#N/A. Start the table at the lowest value you could see, usually 0. - Forgetting the $ signs. Lock the table with
$D$2:$E$6so it does not move when you fill the formula down. - Using FALSE by mistake. FALSE asks for an exact match, so 65 would not be found in a table that has 60.
Next stepTry it in the real Google Sheets
The same pattern works in Excel. Newer versions also have XLOOKUP, which can do approximate matching too and is covered at the Adept level.