Home › Practice questions › VLOOKUP › Question 12

Excel VLOOKUP Practice Question: Sales are in B2:B7 and the commission slabs in D2:E5 (a slab starts at the…

By Rinkesh Shah, founder of Cellgrids.

The question

Sales are in B2:B7 and the commission slabs in D2:E5 (a slab starts at the amount in column D). Find each person's commission rate in G2:G7 with VLOOKUP using an approximate match.

The data

	A	B	D	E	G
1	Salesperson	Sales	Sales from	Commission	Rate
2	Amit	4200	0	0.02	?
3	Neha	12800	10000	0.03	?
4	Rahul	27500	25000	0.05	?
5	Priya	50000	50000	0.07	?
6	Karan	9990			?
7	Sneha	61300			?

The yellow cells are where your answer goes.

Show answer

Expected results:

G2 = 0.02
G3 = 0.03
G4 = 0.05
G5 = 0.07
G6 = 0.02
G7 = 0.07

How it works

Explanation: With TRUE, VLOOKUP finds the largest slab start that is not above the sales amount. The slab table must be sorted from small to large.
Syntax: =VLOOKUP(B2,$D$2:$E$5,2,TRUE)
9990 falls in the 0 slab (2%), 12800 in the 10000 slab (3%), and 50000 exactly starts the 7% slab.

Try it yourself in the live sheet

Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.

Try this question in the live sheet →

VLOOKUP syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Looks up a value in the first column of a range and returns a value from another column, same row. Pulling an employee's salary into another sheet using their ID. Full VLOOKUP guide.