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 all the time and don’t even notice, because the formula never errors out — it just over- or underpays and smiles about it.

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

Where flat-rate lookups quietly cost you money

Any structure with graduated bands — sales commission tiers, progressive tax brackets, bulk-purchase discounts, utility billing tiers, SaaS usage pricing — 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.

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 — a completely different calculation that happens to look just as confident as the correct one, right up until you check the math.

Why VLOOKUP gets it wrong (with receipts)

Let’s use a commission plan with four tiers:

Tier	Threshold	Rate
1	$0	5%
2	$10,000	8%
3	$25,000	12%
4	$50,000	15%

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 just 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:

Deal size	Flat-rate VLOOKUP	Correct graduated total	Overpayment
$8,000	$400.00	$400.00	$0.00
$18,000	$1,440.00	$1,140.00	$300.00
$32,000	$3,840.00	$2,540.00	$1,300.00
$75,000	$11,250.00	$8,450.00	$2,800.00

Below the first threshold, both methods agree — which is exactly why this bug hides so well. It sails through testing on small numbers and only misbehaves once a deal is big enough to cross a second tier. Sneaky.

A hand-drawn line chart climbing in uneven steps from “sucking” to “not sucking,” labeled the past and the future

The fix, two ways

Good news: graduated math has a genuinely elegant native answer — no helper-column jungle, no five-deep nested IF. Here are two ways to get there. Pick the first if this workbook might ever land on an older version of Excel; pick the second if you’re all-in on 365 and want to feel a little smug about it.

Option 1 — SUMPRODUCT: works absolutely everywhere

This is the backward-compatible workhorse. It’s been valid Excel since 2007, so it opens cleanly in any version a colleague, client or auditor might be running:

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

Here’s what’s actually happening: Thresholds is the list of tier starting points, and Increments is each tier’s rate minus the tier before it — not its full rate. For every tier the amount has cleared, (Amount>Thresholds) returns TRUE (that’s a 1 in disguise), so that tier chips in the portion of the amount above its own threshold, times its own incremental rate. Tiers the amount hasn’t reached yet quietly evaluate to FALSE (a 0) and vanish from the sum entirely. Because every tier only ever contributes its own slice, the totals telescope into the right graduated answer automatically. Add a tier, shuffle a threshold — the same one-liner just keeps working.

Build it yourself in 4 steps:

1. Lay out the tier table. Two columns: threshold and marginal rate, sorted ascending, starting at 0.

2. Add a Rate Increment column. First row equals its own rate; every row after subtracts the row above:

D2:  =C2
D3:  =C3-C2

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

4. Enter the formula next to each amount:

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

Copy it down the column and you’re done. No array-entry, no CTRL+SHIFT+ENTER gymnastics, no LAMBDA required — this formula travels safely into a file a colleague opens on Excel 2016.

Option 2 — the Excel 365 spill: write it once, done forever

If you’re on 365, you can skip the “copy the formula down the column” step entirely. Wrap the exact same logic in MAP, point it at the whole amounts range, and Excel spills one correct answer per row automatically — add a new deal size and the results range just grows to match it:

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

MAP takes an array (Amounts, your whole column of deal sizes) and a tiny inline function (LAMBDA) that says “for each amount, run this,” then spills the results down as one dynamic array — the little # badge you see next to a spilled range. Enter it once in the top cell, and it does the copy-down for you, forever. It’s the same trusted SUMPRODUCT math underneath; MAP is just the part that stops you from ever dragging a fill handle again.

Want it even more self-documenting? LET names your intermediate steps so the formula reads like a sentence instead of a wall of parentheses:

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

— same calculation, just easier on the eyes for whoever inherits this sheet next (possibly future-you).

Where else this formula pays for itself

Progressive income tax brackets — the textbook case, and the one most people already get wrong with a single lookup.
Bulk-purchase and volume discounts — “first 100 units at list, next 400 at 10% off” pricing.
Utility and cloud-storage billing tiers — usage-based rates that step up (or down) past thresholds.
Referral and affiliate bonus tiers — payouts that escalate with volume.
SaaS usage-based pricing — API call or seat pricing that changes past a threshold.

Basically: anywhere a rate schedule has more than one band, the naive lookup is quietly lying to you, and this formula is the fix.

Download the workbook

The commission example above — plus a second, independent worked example applying the same formula to simplified progressive tax brackets — lives in a free workbook you can pull apart, poke at, and steal for your own comp plan. Both sheets are built for editing: blue cells are inputs, yellow-filled cells are the tier assumptions, and every result recalculates live the moment you touch a threshold, a rate, or an amount. It ships with the backward-compatible SUMPRODUCT version so it opens cleanly no matter whose Excel it lands in — paste in the MAP version yourself if you’re on 365 and want the one-and-done spill.

Download the tiered commission calculator (.xlsx)

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 math, not a flat rate.
=SUMPRODUCT((Amount>Thresholds)*(Amount-Thresholds)*Increments) computes the correct graduated total in one formula, works in any Excel version, and needs no helper columns.
On Excel 365, wrapping it in MAP(Amounts, LAMBDA(amt, ...)) turns it into a single formula that spills the whole column and never needs copying down again.
The Increments column — each rate minus the one before it — is the piece that makes the formula correct. It’s also the piece most homemade versions of this calculation skip.
This isn’t just a commission trick. Tax brackets, bulk discounts, utility tiers and usage-based pricing are all the exact same shape of problem.