Pull each rep's tier rate so payroll can total commission
Sales ops handed off the quarterly roster. Each rep is tagged with a commission tier; look up the matching rate from the master table on the left so payroll can multiply it out before 4pm.
Tag every rep with their commission rate
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.
Sales ops just handed off the quarterly roster. Each rep is tagged with a tier (Bronze / Silver / Gold / Platinum); you need the matching rate from the master table so payroll can total the commission column.
In cell G2, return the commission rate for the tier listed in F2. Look it up against the master table at A2:B5. Lock the lookup range so the same formula works when dragged through G11.
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.