Tiered commission: lookup mode, unmatched reps, blank revenue
A sales compensation workbook has a Reps sheet with RepID, Name, Region, and Quota; a Deals sheet with DealID, RepID, CloseDate, Product, and Revenue; and a Tiers sheet listing monthly commission rates with lower bounds in column A (0, 50000, 100000, 200000) and rates in column B (2%, 4%, 6%, 8%). You must calculate each rep's monthly commission. Revenue can be blank for open deals, and some Deal rows contain a RepID that is not on the Reps sheet because of a data entry error. The Tiers table is sorted ascending. Management wants a single formula per rep per month, no #N/A errors, and a summary that flags any unmatched RepID. They also want to avoid volatile functions and helper columns on the Deals sheet.
1. Which lookup approach should you use for the commission rate, and what must you clarify before writing it?
Read the worked answer
First clarify the commission rule. If the Tiers rates are flat rates applied to the whole monthly revenue (a step scale), use an approximate-match lookup. With the table sorted ascending, =XLOOKUP(monthlyRevenue,Tiers!$A$2:$A$5,Tiers!$B$2:$B$5,0,-1) returns the rate for the largest lower bound not greater than revenue; the -1 match mode is the key. VLOOKUP(monthlyRevenue,Tiers!$A$2:$B$5,2,TRUE) works in older Excel if the first column is sorted. If instead the tiers are progressive (2% on the first 50,000, 4% on the next 50,000, and so on), a single approximate lookup is wrong; you need a marginal calculation, often SUMPRODUCT over the tier bands. Do not guess: the two methods produce very different payouts. Test one rep by hand at a boundary such as exactly 50,000, because approximate lookups return the lower tier at the exact boundary.
2. How do you handle Deal rows whose RepID is not on the Reps sheet, given the requirement of no #N/A errors?
Read the worked answer
SUMIFS and COUNTIFS ignore unmatched RepIDs rather than returning #N/A, so an unknown rep simply gets zero, which hides the data-entry error. Build a validation column on the Deals sheet: =IF(COUNTIF(Reps!$A:$A,[@RepID])=0,"Unmatched RepID","OK"), or use XLOOKUP with a deliberate if_not_found value. Then filter the Deals table on that column and correct the IDs at the source. On the summary sheet, add a count of unmatched deals with COUNTIF or a PivotTable so the total is visible. Wrap the commission formula in IFERROR only after the validation step, not instead of it, because IFERROR would convert a real logic error into a plausible zero. Keep the validation formula as a column in the Deals table so it extends to new rows; that is not a helper column for the commission calculation, it is a control.
3. How should blank Revenue cells and monthly aggregation be handled in the formula design?
Read the worked answer
Blank Revenue cells are ignored by SUMIFS, so the monthly total is correct, but a lookup or direct multiplication on a blank returns zero or a misleading rate. Use =IF([@Revenue]="",0, ... ) around the commission calculation, or aggregate first with SUMIFS by RepID and month, then apply the tier lookup once to that total. Blank is not the same as zero: an open deal with no revenue should not pull the 0 lower bound and 2% rate. For the monthly summary, use a unique list of RepIDs and months, then SUMIFS with absolute references to the criteria ranges so the formula fills across months without drifting. Avoid OFFSET or INDIRECT, which are volatile and recalculate on every change. Finally, verify totals against the Deals sheet and document the boundary behavior.