Compute the commission
Multiply the sales in E2 by the band rate and round to two decimals.
Formula: =ROUND(E2*VLOOKUP(E2,A2:B5,2,TRUE),2) → 1872
Hands-on Excel exercises
Practice for the MO-201 Excel Expert Exam
Independent practice for skills covered by the MO-201 exam: tiered lookups and logic. Not affiliated with Microsoft; macros not covered.
FormulaCamp is not affiliated with Microsoft. This is independent practice for skills covered by the MO-201 exam. It is not official training, not an official practice test, and not a promise of passing. It does not simulate the desktop application and does not cover macros.
MO-201 is the expert-level Microsoft Excel exam. Its published skill areas include workbook options and settings, data management and formatting, advanced formulas and macros, and advanced charts and tables. As with all certification exams, the objectives and the exam interface are controlled by Microsoft, so use the current skills outline on Microsoft's exam page as the source of truth. This page is independent practice and is not affiliated with Microsoft.
The part FormulaCamp can practice is advanced calculation: tiered lookups, nested logic, approximate matching, and auditing a result. FormulaCamp does not simulate the desktop application, so it cannot practice what only exists there. Workbook settings, protection, dialog-driven features, and printing are not simulated, macros and VBA are not covered, and there is no exam interface or scoring. Rehearse those parts in Excel itself.
The live sheet is a commission table with four sales bands. Your task is to return the rate for a sales figure of 31,200 using an approximate-match lookup, a classic expert-level pattern because the band table must be sorted. The worked answers compute the commission, label the tier with IFS, and repeat the lookup with XLOOKUP in match-next-smaller mode. Use this as a skills workout; it makes no promise about an exam result.
Your turn
Your turn: in F2, return the commission rate for the sales figure in E2 using an approximate-match lookup in the band table.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | |||
| 2 | 0 | 0.02 | 31200 | |||
| 3 | 10000 | 0.04 | ||||
| 4 | 25000 | 0.06 | ||||
| 5 | 50000 | 0.08 | ||||
| 6 |
Not yet solved.
Answer: =VLOOKUP(E2,A2:B5,2,TRUE)
Practice under a time limit
Try the free Sample mock exam with 4 tasks in 8 minutes, or browse the level and interview mock exams included with Pro + Exams.
Related exercises
Open a task in its lesson.
- In G2:G5, look up the price, and show 0 for an unknown SKU.
- In G2:G4, find the employee ID for each name. The ID is to the left of the name, so VLOOKUP will not help.
- Fix D5: the number of units of juice sold.
- In F6, find the weakest result with the SMALL function.
- In I15, use a formula to find the name of the top destination in the first PivotTable.
- In F6, calculate the month's share of the half-year revenue and turn on the "%" format.
Worked answers
Try the spreadsheet first, then compare your formula and result.
Label the tier
Return Gold for 50000 or more, Silver for 25000 or more, otherwise Bronze.
Formula: =IFS(E2>=50000,"Gold",E2>=25000,"Silver",TRUE,"Bronze") → Silver
Repeat the lookup with XLOOKUP
Return the band rate for E2 using the next-smaller match mode, with 0 if nothing applies.
Formula: =XLOOKUP(E2,A2:A5,B2:B5,0,-1) → 0.06
Frequently asked questions
Is this a Microsoft practice test for MO-201?
No. FormulaCamp is not affiliated with Microsoft, and this is independent practice for skills the exam covers.
What is not covered here?
Macros and VBA, workbook options, protection, dialog-driven features, printing, and the exam interface are not simulated.
Why must the band table be sorted?
An approximate-match VLOOKUP assumes ascending order in the first column. Unsorted bands return wrong rates without an error.
Will this help me prepare?
It practices the calculation side. Check Microsoft's current skills outline for everything else, and make no assumption about results.