Commission by attainment tier (approximate lookup)
The problem
Problem statement
Commission is a percentage of bookings, and the percentage depends on attainment. The tier table is in F2:G5, and Ada's quota and bookings are on her row in A2:D2. Put her commission in the Commission cell D2.
The rate must come from the tier table by formula — attainment of exactly 100% earns the 100% tier, not the one below.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | F | G | |
|---|---|---|---|---|---|---|
| 1 | Rep | Quota | Booked | Commission | Attainment from | Rate |
| 2 | Ada | 1000 | 1150 | 0 | 0.02 | |
| 3 | Ben | 1000 | 800 | 0.8 | 0.04 | |
| 4 | Cleo | 1000 | 1300 | 1 | 0.06 | |
| 5 | 1.2 | 0.08 |
Notes
Approximate matching needs the table sorted ascending by the attainment floor. MATCH(attainment, floors, 1) finds the largest floor that is not above it; VLOOKUP with TRUE does the same in one step. The lookup value here is computed (booked ÷ quota), which is why it cannot be a plain equality.
How to approach
Your answer goes in D2.
- Target cell is D2
- Attainment is C2/B2
- The tier table is sorted ascending — use the approximate match
- Shape: =C2*VLOOKUP(?, ?, ?, ?)
Function guide
FAQ
How do I look up a rate by a computed percentage?
Compute attainment inside the lookup: =VLOOKUP(booked/quota, tiers, 2, TRUE). The TRUE option needs the floors sorted ascending.
Why did the commission use the wrong tier?
Approximate matching takes the largest floor not above the value, so an unsorted table or a value below the first floor returns something unexpected rather than an error.