Tiered Commission Formulas in Excel 365: Why VLOOKUP Gets It Wrong

Okay, real talk: if you’ve ever built a commission plan, a bulk-discount schedule or a tax provision in Excel, you’ve probably written this exact formula — a VLOOKUP against a tier table.

It looks confident. It returns a nice, clean number. And that number is quietly, cheerfully wrong, because it slaps one rate on the entire amount instead of giving each slice of it the rate it actually earned.

Sales ops, RevOps and finance folks run into this constantly and don’t notice, because the formula never errors out. It just over- or underpays and smiles about it.

Grab a coffee, we’re doing this properly: the formula that always works, then the Excel 365 version that spills down the column all by itself.

Who this quietly bites

Any structure with graduated bands plays by the same rule: the first slice of the amount gets the first rate, the next slice gets the next rate, and so on up the ladder. Only the portion inside each band gets that band’s rate. Nothing more.

• Sales commission tiers — the case that costs real money, because it pays out monthly and nobody re-derives it.
• Progressive tax brackets — the textbook case, and the one most people already get wrong in conversation as well as in Excel.
• Bulk-purchase and volume discounts — “first 100 units at list, next 400 at 10% off” pricing.
• Utility and cloud billing tiers — usage-based rates that step up, or down, past thresholds.
• Referral and affiliate bonuses — payouts that escalate with volume.

A single VLOOKUP — or a lone IF — simply can’t say that. It finds the highest tier the amount has reached and multiplies the whole amount by that one rate. That’s a completely different calculation that happens to look just as confident as the correct one.

Why VLOOKUP gets it wrong (with receipts)

Let’s use a commission plan with four tiers:

A rep closes an $18,000 deal. The correct payout is 5% on the first $10,000 plus 8% on the remaining $8,000 — $1,140. A flat-rate VLOOKUP sees “$18,000 lands in the 8% tier” and pays 8% on the whole thing — $1,440.

That’s $300 handed out on a single deal that shouldn’t exist, and the gap only gets more embarrassing as the numbers grow:

The fix, two ways

The scenario, in full. The four-tier table above in B8:C11, an increment column in D8:D11, and eight deal sizes in B15:B22 running from $8,000 to $120,000.

Graduated maths has a genuinely elegant native answer — no helper-column jungle, no five-deep nested IF.

Option 1 — Classic (backward-compatible functions, step by step)

SUMPRODUCT has been valid Excel since 2007, so this opens cleanly in any version a colleague, client or auditor might be running.

Step 1 — Lay out the tier table.

Two columns, threshold and marginal rate, sorted ascending and starting at zero. Both of those matter: the formula reads the table as “everything above this point earns at least the next increment,” so a gap or an out-of-order row changes the answer without erroring.

Step 2 — Add a Rate Increment column.

First row equals its own rate; every row after subtracts the row above:

D8:  =C8
D9:  =C9-C8

Step 3 — Name the ranges.

Select the threshold column → Formulas → Define Name → Thresholds. Repeat for the increment column → Increments. Named ranges make the formula readable and survive you inserting a new tier row later.

Step 4 — Enter the formula next to each amount.

=SUMPRODUCT((B15>Thresholds)*(B15-Thresholds)*Increments)

Copy it down the column and you’re done. No array entry, no Ctrl+Shift+Enter, no LAMBDA required — this travels safely into a file a colleague opens on Excel 2016.

The other thing worth knowing before you ship it:

Option 2 — Excel 365 spill (dynamic-array equivalent)

On 365 you can skip the copy-down step entirely. Wrap the same logic in MAP, point it at the whole amounts range, and Excel spills one correct answer per row:

=MAP(Amounts, LAMBDA(amt, SUMPRODUCT((amt>Thresholds)*(amt-Thresholds)*Increments)))

MAP takes an array — your whole column of deal sizes — and a tiny inline function that says “for each amount, run this,” then spills the results as one dynamic array. Enter it once in the top cell and it does the copy-down for you, forever. It’s the same trusted SUMPRODUCT maths underneath; MAP is only the part that stops you dragging a fill handle.

Better still, name the intermediate results and produce the whole comparison in one cell:

=LET(
   amt,  Amounts,
   grad, MAP(amt, LAMBDA(x, SUMPRODUCT((x>Thresholds)*(x-Thresholds)*Increments))),
   flat, MAP(amt, LAMBDA(x, x*LOOKUP(x, Thresholds, Rates))),
   HSTACK(amt, grad, flat, flat-grad)
)

Four columns — amount, correct, flat-rate, overpayment — from a single formula, with the overpayment derived by subtracting two named results rather than kept in step by a second formula.

Want to see the telescoping happen? Drop the SUM and spill the slices instead:

=LET(
   amt,  $B$15,
   slab, (amt>Thresholds)*(amt-Thresholds)*Increments,
   HSTACK(Thresholds, Rates, Increments, slab)
)

One row per tier, showing exactly what each one contributed. That block is the fastest way to explain this formula to somebody who doesn’t believe it — and the fastest way to spot a broken tier table, because a tier contributing a suspiciously round number is usually a tier whose increment is really a rate.

For a single amount, LET also makes the one-liner read like a sentence:

=LET(amt, A2, slices, (amt>Thresholds)*(amt-Thresholds)*Increments, SUM(slices))

Same calculation, considerably kinder to whoever inherits the sheet — possibly future you.

One decision to make deliberately

(Amount>Thresholds) is strictly greater-than. An amount sitting exactly on a threshold stays entirely in the band below it: $10,000 pays 5% on all of it, and the 8% starts at $10,000.01.

That’s almost always what a comp plan means, and it’s what makes the ladder continuous — one dollar over the line earns eight cents, not a $300 cliff. But it is a choice, and >= is a legitimate different one. Pick it on purpose, write it down, and test the boundary.

Where else this pattern pays for itself

• Progressive income tax brackets — the identical formula, pointed at a different table. The workbook does exactly that on its own sheet, and prints effective rate beside marginal rate so the difference has a number.
• Bulk-purchase and volume discounts — “first 100 units at list, next 400 at 10% off.”
• Utility and cloud-storage billing tiers — including the ones that step down, which the same formula handles with negative increments.
• Referral and affiliate bonus tiers — payouts that escalate with volume.
• SaaS usage-based pricing — API calls or seats priced past a threshold.

Download the workbook

The commission example above — plus a second, independent worked example applying the identical formula to simplified progressive tax brackets — in a workbook you can pull apart and steal for your own comp plan.

Five sheets: Commission Calculator carries the tier table with its Rate Increment column and eight deal sizes priced both ways, with the overpayment beside each. Tax Bracket Example runs the same formula on tax bands, with effective rate against marginal rate. 365 (MAP) spills the whole comparison from one cell and breaks a single amount down slice by slice, so you can watch the telescoping. Check proves the one-liner against a hand-built slice calculation with the tier boundaries hardcoded — a genuinely independent route to the same number — tests the four boundary cases where this kind of formula usually breaks, and prices the wrong answers, including the increments-versus-rates mistake.

Yellow cells are the inputs: change a threshold, a rate or a deal size and everything recalculates live.

Download the tiered commission calculator (.xlsx)

The 365 (MAP) sheet needs Microsoft 365 or Excel 2021+. Everything else opens in Excel 2007 onwards.

Takeaways

• A single VLOOKUP or IF against a tier table answers “what’s the top rate?” — not “what’s actually owed?” Graduated structures need marginal maths.
• =SUMPRODUCT((Amount>Thresholds)*(Amount-Thresholds)*Increments) computes the correct graduated total in one formula, in any Excel version, with no helper columns.
• The Increments column — each rate minus the one before it — is the piece that makes it correct. It’s also the piece most homemade versions skip, and pointing this formula at full rates instead overpays badly.
• It agrees perfectly below the first threshold, which is why it passes testing. Test above your highest threshold or you have tested nothing.
• On 365, MAP(Amounts, LAMBDA(amt, ...)) spills the whole column, and LET + HSTACK will produce the correct figure, the flat-rate figure and the gap between them from a single cell.
• Strictly greater-than is a decision. An amount exactly on a threshold stays in the band below. Pick it deliberately and test the boundary.
• Your move: take your highest-value deal from last quarter and price it both ways. If the two numbers differ, that difference has already been paid.