Excel track · Exercise 82 of 86 Paid

A commission sheet that updates itself

Read a rate from a banded table, then multiply it by the sales figure.

Advanced

Three sellers beside a three-band rate table below the data

Read-only data · screen 1 of 2 · answer in C2

In C2, pay the first seller their sales times the tier rate those sales reach.

	A	B	C
1	Seller	Sales	Commission
2	Ana	4200
3	Bo	1800
4	Cy	9000
5
6	Tier	Rate
7	0	0.02
8	2000	0.03
9	4000	0.05

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In C2, report the rate the first seller's sales figure has reached as a decimal.

	A	B	C
1	Seller	Sales	Rate
2	Ana	4200
3	Bo	1800
4	Cy	9000
5
6	Tier	Rate
7	0	0.02
8	2000	0.03
9	4000	0.05

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

The fourth argument of VLOOKUP decides how it matches. With TRUE, it walks down column A, stops at the largest tier floor the sales figure has reached, and returns the rate beside it. Sales of 4200 reach the 4000 floor, so the rate comes back at five per cent.

In C2 that rate multiplies the sales figure, so Ana's commission is 210. The second screen asks for the rate alone, 0.05, so the lookup and the multiplication come apart into two steps. Keep the rate in the table: 0.05 typed into a formula works today and breaks the next time finance moves a tier.

The table has to stay sorted by tier floor, lowest first, because TRUE reads down the column and stops at the last floor it passes. An unsorted table returns a rate from the wrong band and shows no error. Lock the reference as $A$7:$B$9 so the formula fills down the commission column without drifting.

Syntax

=sales*VLOOKUP(sales,tiers,2,TRUE) reads the rate the sales figure has reached

Worked example

On Three sellers beside a three-band rate table below the data, =ROUND(B2*VLOOKUP(B2,$A$7:$B$9,2,TRUE),2) returns 210.

=ROUND(B2*VLOOKUP(B2,$A$7:$B$9,2,TRUE),2)

Common errors

Error codes for VLOOKUP in approximate mode as a rate band, composed with multiplication
Error	What it means and how to fix it
`#N/A`	The fourth argument is FALSE, so VLOOKUP looks for a row equal to 4200 and finds none.
`#REF!`	The column number points past the second column of the rate table.
`Wrong band`	The tier floors are out of order, so TRUE stops at the wrong floor and reads the wrong rate.

At work

Paying commission from a banded rate table that finance maintains below the data.