Bands and approximate match: practice exercises
VLOOKUP with TRUE, XLOOKUP match mode -1. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
The table in G1:H6 gives the lowest appraisal score for each rating. In C2, look up each person's rating. Your formula will be copied down to C13.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Score | Rating | From | Rating | |||
| 2 | Lucy | 78 | 0 | Below | ||||
| 3 | Maria | 55 | 11 | Developing | ||||
| 4 | Femi | 24 | 21 | Good | ||||
| 5 | Sunil | 11 | 55 | Strong | ||||
| 6 | Kerry | 20 | 90 | Outstanding | ||||
| 7 | Ollie | 51 | ||||||
| 8 | Ivan | 90 | ||||||
| 9 | Priya | 81 | ||||||
| 10 | Rachel | 21 | ||||||
| 11 | Ben | 47 | ||||||
| 12 | Nia | 10 | ||||||
| 13 | Tom | 64 |
How it works
A band table lists the lowest value for each band. VLOOKUP(score, table, 2, TRUE) finds the last boundary that's less than or equal to the score, so a score of 47 falls in the band that starts at 40. The boundaries must be in ascending order.
XLOOKUP(score, boundaries, bands, , -1) does the same job: match mode -1 means "exact match, or the next smaller". It works whatever order the table is in.
=VLOOKUP(B2,$E$2:$F$4,2,TRUE) =XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,,-1)
Band tables
Grades, ratings and commission rates usually come from a band table that lists the lowest value for each band: 0 is Low, 50 is Mid, 80 is High.
A score of 47 isn't in the table, so an exact lookup can't find it. An approximate match finds the last boundary that isn't bigger than the score, which puts 47 in the band that starts at 0.
Worked example: VLOOKUP with TRUE
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Score | Band | Band (XLOOKUP) | From | Band |
| 2 | Amira | 47 | 0 | Low | ||
| 3 | Ben | 80 | 50 | Mid | ||
| 4 | Cara | 92 | 80 | High | ||
| 5 | Dev | 12 |
=VLOOKUP(B2,$E$2:$F$4,2,TRUE)in . Look up the score in the locked band table and return column 2. TRUE at the end means approximate match. 47 hasn't reached 50, so it's Low.=VLOOKUP(B2,$E$2:$F$4,2,TRUE)in . Ben's 80 is exactly on a boundary, so High: a score on the boundary is in the band that starts there.
Worked example: XLOOKUP
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Score | Band | Band (XLOOKUP) | From | Band |
| 2 | Amira | 47 | 0 | Low | ||
| 3 | Ben | 80 | 50 | Mid | ||
| 4 | Cara | 92 | 80 | High | ||
| 5 | Dev | 12 |
=XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,,-1)in . XLOOKUP does the same job with match mode -1: exact match, or the next smaller. The empty space between the commas is the "if not found" input, left blank.
Watch out
- An approximate VLOOKUP needs the boundaries in ascending order. If they're not, it quietly gives wrong answers. XLOOKUP with -1 doesn't mind the order.
- FALSE (exact match) is for codes and names. TRUE (approximate) is for bands. Mixing them up is a common mistake.
What you'll practise
- The table in G1:H6 gives the lowest appraisal score for each rating. In C2, look up each person's rating. Your formula will be copied down to C13.
- Do the same with XLOOKUP. In C2, find each person's rating from the scores in G2:G6 and the ratings in H2:H6.
- Commission is paid at a rate that depends on how much someone sold (table in G1:H5). The rate applies to all their sales. In C2, work out each person's commission: sales × their rate.
- Someone typed the rating table highest first, so the VLOOKUP in C2 goes wrong: an approximate VLOOKUP needs the boundaries in ascending order. Fix the formula without moving the table.