Run quarterly commission with the target kicker
Each rep's rate comes from their tier (A/B/C/D in the master on the left). Reps who hit their Q3 target get a 20% kicker on top of the base commission. Payroll wants the final number rounded to cents — no fractional pennies — so all three functions matter.
Quarterly commission with target bonus
Practice using VLOOKUP to solve a real finance & accounting problem. By the end, you’ll know when to reach for it and how to structure the arguments correctly.
Commission run at 4pm AND quarter close. Each rep's rate comes from their tier (A/B/C/D in the master table). Reps who hit their Q3 target get a 20% kicker on top of base commission. Payroll wants the final number rounded to cents — no fractional pennies.
In H2, compute the commission: multiply Sales (F2) by the tier rate (look up E2 in the master table), apply a 1.2× multiplier when 'Hit Target' (G2) is 'Y', and round the whole thing to 2 decimals. Lock the tier table with $ signs and drag down through H7.
VLOOKUP searches the first column of a table for a value, then returns a value from a column you specify to the right. It's the most common lookup pattern in Excel: 'find this ID, return the matching name'. The fourth argument controls the match type, and almost always wants to be FALSE for exact match.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Try the formula first. Hints cost CP for a reason.