Excel Practice
Lessons Lesson 42

Approximate Match: Bands and Brackets

About this lesson

An approximate match is VLOOKUP's TRUE or XLOOKUP's match mode -1. It returns the row whose first-column value is the largest one not above the lookup value. That is how a band, a tax bracket or a shipping rate is found.

By the end you can

  • look up a band, bracket or rate with an approximate match.
  • say what the table must look like for TRUE to be safe.
  • do the same with XLOOKUP's match mode -1.

Practises VLOOKUP XLOOKUP MATCH

The idea

Exact match answers "which row is this code". Approximate match answers "which band does this number fall in". The table lists the lower bound of each band in ascending order. The lookup stops on the last row whose bound the value has reached. Tax brackets, postage by weight, grade boundaries and commission tiers are all this one lookup.

Two rules. The first column must be sorted ascending. VLOOKUP with TRUE does not check, and it returns wrong answers that look right on an unsorted table. And the last band is open at the top. Anything beyond the final bound gets the final row. So a table that should refuse very large values needs a row that says so.

The mistake to watch for

An unsorted table. VLOOKUP with TRUE does not check that the first column is ascending. On a table that is not, it returns wrong answers that look right, with no error. Sort the boundary column. And remember the last band is open at the top. Anything beyond the final bound gets the final row, unless the table has a row that says otherwise.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Parcel P1 weighs 1.4 kg, and there is no row for 1.4 in the band table. In C2, find its rate with VLOOKUP and TRUE as the last argument. Then the lookup takes the band the weight falls into. Lock the table.

To begin, type it exactly:

=VLOOKUP(B2,$E$2:$G$5,3,TRUE)

C2
Row A B C D E F G
1 Parcel Kg Rate From kg Band Rate
2 P1 1.4 0 Light 2.5
3 P2 6 2 Medium 4
4 P3 2 5 Heavy 7.5
5 P4 30 10 Bulky 12
6
7
8

Every step

  1. 1

    Parcel P1 weighs 1.4 kg, and there is no row for 1.4 in the band table. In C2, find its rate with VLOOKUP and TRUE as the last argument. Then the lookup takes the band the weight falls into. Lock the table. To begin, type it exactly: =VLOOKUP(B2,$E$2:$G$5,3,TRUE).

    Hint. Column 3, and TRUE.

  2. 2

    Read =VLOOKUP(B3,$E$2:$G$5,2,TRUE) and say which band it returns for the 6 kg parcel.

    Hint. Largest lower bound not above 6.

  3. 3

    P3 weighs exactly 2 kg, a boundary. Read =VLOOKUP(B4,$E$2:$G$5,2,TRUE) and say which band it returns.

    Hint. Equal counts as reached.

  4. 4

    P4 weighs 30 kg, more than any row. In C5, look up its band name with an approximate match. See what the lookup does past the end of the table.

    Hint. Column 2 this time.

  5. 5

    C3 was written with an exact match and shows #N/A, because no row says 6. Fix it.

    Hint. The last argument.

  6. 6

    In D2, write the P1 rate again with XLOOKUP. Use a match mode of -1, which means exact match or the next smaller value.

    Hint. Leave the fourth argument empty; -1 is the fifth.