Administration and Executive AssistantTier 340 minintermediate

Band Lookups

Assign rates, grades and tiers from a table of thresholds instead of a wall of nested IFs.

What you will learn

• Build a threshold table sorted ascending
• Use an approximate-match lookup to find the band a value falls into
• Test the boundary values that sit exactly on a threshold
• Change a band by editing the table, not the formula

Excel skills

VLOOKUP with TRUE, XLOOKUP match_mode -1, MATCH type 1 · Approximate Matching

The task

Commission is paid by band, from the Bands tab: from R0 it is 2%, from R20 000 it is 3.5%, from R50 000 it is 5%, and from R100 000 it is 6.5%. "From" means the threshold itself is in the higher band. Work out each rep's rate and commission. Finance changes the bands every year, so the rates must come from the Bands tab, not be typed into formulas.

You work in a real spreadsheet in your browser, and your answer is checked against hidden traps the moment you submit.

Try this lesson free