Formuladrill

Level 3: Lookups Exercise 51 of 118

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.

Functions used: XLOOKUP

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 exercise

More exercises in Lookups