How to calculate commission with tiered percentage rates in Excel

Q: How do I show the rate used?

Use the lookup on its own: =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1).

Formula (Excel and Google Sheets)

=B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)

Look up the rate for the sales amount in a tier table, then multiply: =B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1).

Result on the example: one result per row in C2, filled down to C6
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in C2, filled down to C6. Change any input and the result updates, or edit the formula to experiment.

How it works

The tier table lists where each rate starts. XLOOKUP's match mode -1 finds the exact amount or the next smaller one, so 32,000 lands in the tier that starts at 25,000 and gets 6%. Multiplying by the sales gives the commission. Ben's 10,000 is exactly on a tier start and gets 4%.

Here the whole amount earns the tier's rate. If only the part above each threshold earns the higher rate, see the FAQ.

=B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)

Result170

B2$8,500.00
The value in B2.
$E$2:$E$54 rows × 1 col: $0, $10,000, $25,000, $50,000
A range: 4 cells in one column.
$F$2:$F$54 rows × 1 col: 2%, 4%, 6%, 8%
A range: 4 cells in one column.
-1-1
Makes the value negative.
XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)0.02
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueB2$8,500.00
- lookup_array$E$2:$E$54 rows × 1 col: $0, $10,000, $25,000, $50,000
- return_array$F$2:$F$54 rows × 1 col: 2%, 4%, 6%, 8%
- if_not_found(omitted)
- match_mode-1-1
B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)170
The * operator multiplies.

In Google Sheets

The same formula works in Google Sheets: =B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=B2*VLOOKUP(B2,$E$2:$F$5,2,TRUE)	$170.00, $400.00, $1,920.00…	Works in every version: approximate-match VLOOKUP does the same tier search, as long as the table is sorted from lowest to highest.

Common mistakes

The tier table must start at 0, or amounts below the first threshold return #N/A.
With VLOOKUP, the thresholds must be sorted ascending; XLOOKUP with -1 doesn't need that.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate commission with tiered percentage rates

In C2, calculate the commission using the rate of the tier the sales amount falls in, then fill down to C6.

Questions

How do I pay each rate only on the part of sales within its tier?

Add up each tier's slice with a rate-difference column G (2%, 2%, 2%, 2%): =SUMPRODUCT((B2>$E$2:$E$5)*(B2-$E$2:$E$5)*$G$2:$G$5).

How do I show the rate used?

Use the lookup on its own: =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.