Commission tiers with XLOOKUP
An Excel formula exercise from Formuladrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.
The task
Find each rep's commission rate from the tier table in E:F, where each tier starts at the sales figure in column E. Use XLOOKUP with the match mode that returns an exact match or the next smaller item. Fill C2 and C3.
What this exercise teaches
Match mode -1 does what VLOOKUP does with TRUE, and it says so in the formula. Sales of 18,000 are not in the table, so XLOOKUP falls back to the 10,000 tier.
A hint if you get stuck
The fifth argument of XLOOKUP is the match mode: 0 is exact, -1 is exact or next smaller, 1 is exact or next larger. Leave the fourth argument empty with two commas.
Try it now, free, no account needed
Your first 15 exercises on Formuladrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.
Open this exerciseMore exercises in Lookups
- Find the price
- Look left with INDEX and MATCH
- When the code does not exist
- Tax bracket lookup
- Finishing position
- Price grid
- Return from the left
- Price list into an order
- A lookup inside IFERROR
- Who sold the most
- A column number that moves
- The most recent price
- INDEX and MATCH on two conditions
- Everyone in one department
- Third place