Lookup & Reference
Variable sales commission split between AE and Solutions Engineer (XLOOKUP)
Allocate a deal commission pool dynamically across Account Executive and Solutions Engineer roles using a tiered deal-size split table.
Automate AE / SE split commissions as enterprise deal size scales!
This fictional commission policy splits a pool based on deal gross margin, not sales revenue. The example rates are configurable business assumptions, not industry standards. The tier applies to the entire pool, not marginal slices.
Round the pool to cents first, round the AE share, then assign the remaining cents to SE. Rounding both shares independently can overpay or underpay the pool by a cent. The residual policy deliberately assigns rounding differences to SE.
Thresholds must start at zero and strictly increase. Each split is between zero and one and each row must total one within a small floating-point tolerance. Missing or invalid configuration produces an explicit error message.
| Deal Margin Tier (E) | AE Split % (F) | SE Split % (G) | Example Payout on 10% Pool |
|---|---|---|---|
| $0+ (SMB) | 80% (0.80) | 20% (0.20) | $30k deal -> $2,400 AE / $600 SE |
| $50,000+ (Mid-Market) | 70% (0.70) | 30% (0.30) | $80k deal -> $5,600 AE / $2,400 SE |
| $100,000+ (Enterprise) | 60% (0.60) | 40% (0.40) | $150k deal -> $9,000 AE / $6,000 SE |
Account Executive payout = Commission Pool * Tiered AE Split %
match_mode = -1 steps down to the highest threshold <= deal margin B2.
=IF(OR(COUNT(B2:C2)<>2,COUNT(E2:G4)<>9),"Invalid inputs",IF(OR(B2<0,C2<0,C2>1,E2<>0,E3<=E2,E4<=E3,MIN(F2:G4)<0,MAX(F2:G4)>1,ABS(F2+G2-1)>0.000000001,ABS(F3+G3-1)>0.000000001,ABS(F4+G4-1)>0.000000001),"Invalid inputs",ROUND(ROUND(B2*C2,2)*XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1),2)))Solutions Engineer receives the rounded pool remainder
G2:G4 documents the companion shares and is validated against F2:F4. The payout uses pool minus AE rather than a separately rounded percentage. This keeps the two payouts equal to the rounded pool.
=IF(OR(COUNT(B2:C2)<>2,COUNT(E2:G4)<>9),"Invalid inputs",IF(OR(B2<0,C2<0,C2>1,E2<>0,E3<=E2,E4<=E3,MIN(F2:G4)<0,MAX(F2:G4)>1,ABS(F2+G2-1)>0.000000001,ABS(F3+G3-1)>0.000000001,ABS(F4+G4-1)>0.000000001),"Invalid inputs",ROUND(ROUND(B2*C2,2)-ROUND(ROUND(B2*C2,2)*XLOOKUP(B2,$E$2:$E$4,$F$2:$F$4,0,-1),2),2)))Try the example
Use the original values or change them to test your understanding.