# How to calculate commission with tiered percentage rates in Excel

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)`.

**Excel:** `=B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Rep | Sales | Commission |  | Sales from | Rate |
| Ana | 8500 |  |  | 0 | 0.02 |
| Ben | 10000 |  |  | 10000 | 0.04 |
| Cara | 32000 |  |  | 25000 | 0.06 |
| Dev | 49999 |  |  | 50000 | 0.08 |
| Eli | 75000 |  |  |  |  |

Formula in C2, filled to C6: `=B2*XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,,-1)`

Result: $170.00; $400.00; $1,920.00; $2,999.94; $6,000.00

## 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.

## Other ways

- `=B2*VLOOKUP(B2,$E$2:$F$5,2,TRUE)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/tiered-commission