Tiered Sales Commission

All versionsVLOOKUP

Commission rates that step up with sales are a classic lookup job. A VLOOKUP in approximate-match mode reads a tier table and returns the right rate — change the tiers in the table, not the formula.

Quick formula: with a tier table in E2:F5 (threshold, rate) sorted ascending:

=B2 * VLOOKUP(B2, $E$2:$F$5, 2, TRUE)

TRUE finds the highest threshold not exceeding the sales figure, returning that tier’s rate; multiply by sales for the commission.

Functions used (tap for the full reference guide):

VLOOKUP IFS

The example

Sales of $32,000 falls in the 6% tier.

	A	B
1	Tier from	Rate
2	$0	3%
3	$10,000	5%
4	$25,000	6%
5	Sales $32,000 → commission	$1,920

The formula

Look up the rate, then apply it:

=B2 * VLOOKUP(B2, $E$2:$F$5, 2, TRUE) // $32,000 → 6% → $1,920

How it works

Approximate-match VLOOKUP is built for tier tables:

Build a tier table: the lower threshold of each band in the left column, the rate in the next — sorted ascending.
VLOOKUP(sales, table, 2, TRUE) finds the largest threshold that is ≤ the sales figure and returns its rate.
Multiply the rate by sales for the commission. To change the plan, edit the table — the formula never changes.
For a handful of fixed tiers with no table, IFS works too: =IFS(B2>=25000,0.06, B2>=10000,0.05, TRUE,0.03).

Marginal (progressive) commission? The lookup above pays one rate on the whole amount. To pay each tier’s rate only on the dollars within that band (like tax brackets), use a bracket-style SUMPRODUCT — see the tax-bracket recipe.

Try it: interactive demo

Live demo

Enter a sales figure; see the tier and commission.

Sales $

Rate · Commission

Variations

IFS, no table

For a few fixed tiers:

=B2 * IFS(B2>=25000,0.06, B2>=10000,0.05, TRUE,0.03)

Flat bonus above target

Add a kicker:

=B2*rate + IF(B2>=50000, 1000, 0)

Cap the commission

Limit the payout:

=MIN(B2*rate, 5000)

Pitfalls & errors

Sort the table ascending. Approximate-match VLOOKUP requires the threshold column sorted low-to-high, or it returns the wrong tier.

Use the lower bound of each band. The left column is the start of each tier (0, 10000, 25000), not the upper limit.

Whole-amount vs marginal. This pays one rate on all sales. If your plan is progressive, use a bracket calculation instead.

Practice workbook

📊

Download the free Tiered Sales Commission practice workbook

A commission calculator with a VLOOKUP tier table, the IFS, bonus, and cap variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate tiered commission in Excel?

Build an ascending tier table (threshold, rate) and use =sales * VLOOKUP(sales, table, 2, TRUE). Approximate match returns the rate for the band the sales fall into.

Can I do it without a lookup table?

Yes, for a few tiers: =sales * IFS(sales>=25000,0.06, sales>=10000,0.05, TRUE,0.03).

How do I pay each tier's rate only on the dollars in that band?

That's a progressive/marginal calculation — use a bracket-style SUMPRODUCT like the tax-bracket recipe, not a single VLOOKUP rate.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: VLOOKUP · IFS