Home › Practice questions › XLOOKUP › Question 6

Excel XLOOKUP Practice Question: Using XLOOKUP with an approximate match, find the commission rate for each rep…

By Rinkesh Shah, founder of Cellgrids.

The question

Using XLOOKUP with an approximate match, find the commission rate for each rep from the slab table in E2:F6 and put it in C2 to C4.

The data

	A	B	C	E	F
1	Rep	Sales	Rate	From	Rate
2	Asha	45000	?	0	0
3	Ben	120000	?	50000	0.02
4	Chitra	78000	?	75000	0.04
5				100000	0.06
6				150000	0.08

The yellow cells are where your answer goes.

Show answer

Expected results:

C2 = 0
C3 = 0.06
C4 = 0.04

How it works

Explanation: Match mode -1 returns the exact match or, if there is none, the next smaller value. That is how slab tables are looked up.
Syntax: =XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,"",-1)
45000 falls in the 0 slab, so 0. 120000 falls in the 100000 slab, so 0.06. 78000 falls in the 75000 slab, so 0.04.

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 →

XLOOKUP syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

The modern replacement for VLOOKUP — searches any column and returns from any other, no column counting needed. XLOOKUP is used to pull matching data from one part of a workbook into another. Full XLOOKUP guide.