Audit a commission payout against two tables (VLOOKUP + IFERROR)
The problem
Problem statement
Booking and rate are in B2:C2, so row 2's commission can be recomputed. What payroll actually paid is in the second table at E2:F4, and some reps are missing from it. Put the difference (computed − paid) in the Difference cell G2.
A rep who is not in the payout table was paid nothing — that is a difference, not an error.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Bookings | Rate | Computed | Rep | Paid | Difference |
| 2 | Ada | 10000 | 0.05 | Ada | 500 | ||
| 3 | Ben | 20000 | 0.04 | Ben | 700 | ||
| 4 | Cleo | 5000 | 0.06 | Zoe | 300 |
Notes
Two lookups in one formula: compute the commission from the first table, find the paid amount in the second, subtract. IFERROR covers the rep who never appears in the payout run; treating that as 0 turns a broken #N/A into the very finding the audit is looking for.
How to approach
Your answer goes in G2.
- Target cell is G2
- Computed commission is B2*C2
- Look the rep up in the payout table
- Shape: =?*?-IFERROR(?, 0)
Function guide
FAQ
How do I reconcile a commission payout?
Recompute the commission (bookings × rate), look up what was paid, and subtract. A missing payer should read as 0, not #N/A.
Why not VLOOKUP for the paid amount?
It works when the payout table has one row per rep. If a rep can be paid twice, VLOOKUP returns only the first row; SUMIF adds them.