Approximate match for tiers and bands

Assign discount tiers from a sorted bracket table, and lock the table before copying down. Intermediate · 5 min · VLOOKUP, Approximate match, $ references

Not every lookup wants an exact match. Discounts, shipping rates and grade bands work in brackets: every order from $500 up to $999.99 gets the same rate, and you could never list every possible total.

With TRUE as the last argument (or with it left out), VLOOKUP does an approximate match. It finds the largest value in the first column that is less than or equal to the lookup value, and returns from that row.

> Approximate match only works when the first column is sorted from smallest to largest. Start the table at 0 so that even the smallest value lands in a bracket.

Steps

Harbour Café ordered $640. In C2, find its discount in the tier table G2:I6: =VLOOKUP(B2, $G$2:$I$6, 2, TRUE). The dollar signs lock the table so you can copy the formula down next.
Copy C2 down to C8 to find the discount for every order.
In D2:D8, return each order's tier name from column 3 of the table. This time you can leave out the last argument, because TRUE is the default.
Finally, in E2:E8, work out what each customer pays: the order total multiplied by (1 minus the discount).

Remember

TRUE (or leaving the argument out) finds the largest value that is less than or equal to the lookup value.
The first column must be sorted ascending, starting from the lowest possible value (often 0).
Lock the table with $ before copying the formula down.
Use FALSE for codes and names, TRUE for numeric bands.

Previous: VLOOKUP: exact matches · Next: INDEX and MATCH