Confident Cells

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.

Your turn

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.

ABCDEFGH
1EmployeeScoreRatingFromRating
2Lucy780Below
3Maria5511Developing
4Femi2421Good
5Sunil1155Strong
6Kerry2090Outstanding
7Ollie51
8Ivan90
9Priya81
10Rachel21
11Ben47
12Nia10
13Tom64

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

ABCDEF
1NameScoreBandBand (XLOOKUP)FromBand
2Amira470Low
3Ben8050Mid
4Cara9280High
5Dev12
  1. =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.
  2. =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

ABCDEF
1NameScoreBandBand (XLOOKUP)FromBand
2Amira470Low
3Ben8050Mid
4Cara9280High
5Dev12
  1. =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

  1. 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.
  2. Do the same with XLOOKUP. In C2, find each person's rating from the scores in G2:G6 and the ratings in H2:H6.
  3. 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.
  4. 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.