The Lookup That Ties to the Dollar
A monthly management pack maps margin onto a 380-row customer master using VLOOKUP from a 4,200-row sales table. The pack's total gross margin of $12.84m ties to the GL to the dollar, but three customers show #N/A, and Customer 4471 shows a 31% margin against 22% last month. The workbook was rebuilt after an ERP migration changed customer IDs from numeric to 10-character text. The $12.84m total is a SUMIFS run directly on the sales table by GL account; the customer chart is built from the VLOOKUP-enriched tab. A colleague argues the total ties, so the customer-level allocation must be fine. The CFO wants the pack issued, and the commercial team is about to reprice the top 20 accounts off these margins.
1. How can the total tie to the dollar while the customer-level margins are wrong?
Read the worked answer
- They are two independent calculations. The $12.84m is a SUMIFS on the raw sales table by GL account. The customer chart reads from a separate tab where VLOOKUP has attached a margin rate to each row. The total proves the sales table is complete; it says nothing about the lookup.
- This is the classic false assurance. A control that ties at the total does not validate a join that operates at the row grain. The correct test is a grain-level reconciliation: the sum of the enriched tab must equal the sum of the source table, and the row counts must match, before any total-level tie-out means anything.
- What the lookup is probably doing: if match_type is omitted it defaults to TRUE (approximate), which on unsorted text keys returns a lexicographic 'nearest lower' match. After the migration the keys are 10-character text, so 'CUST-004471' can land between two adjacent keys and pick up a neighbouring customer's rate. That is exactly how Customer 4471 jumps to 31%.
- The #N/A rows: numeric-vs-text mismatches or trailing spaces from the ERP extract. If they are wrapped in IFERROR(...,0), those customers are silently carried at zero margin.
2. What checks would have caught this before the pack went out, and what do you run now?
Read the worked answer
- Key integrity checks on the sales table: COUNTIF of each key in the master must equal 1 (uniqueness); COUNT of ISNA on the join must be 0; LEN and ISTEXT applied to the key column to confirm the migration produced a consistent type; TRIM plus a normalisation column to kill trailing spaces.
- Grain reconciliation: row count of the enriched tab equals row count of the source; SUM of the enriched margin column equals SUM of the source margin column. Any difference is a join failure, not a rounding issue.
- Reversal test: re-run the join in the other direction (master to sales) and confirm every master row with activity appears.
- Analytical overlay: flag any customer whose margin moves more than ~200-300bp month on month. Customer 4471 moving 900bp should have surfaced on the first read of the pack, before any formula audit.
- Right now: count the #N/A rows, quantify the revenue they represent, and check whether the top 20 accounts - the ones about to be repriced - sit on correct rates. If they do not, the repricing decision is downstream of a broken input, which is a commercial risk, not a presentation risk.
3. How do you rebuild the join and what do you tell the CFO?
Read the worked answer
- Rebuild: replace VLOOKUP with XLOOKUP (match_mode 0) or INDEX/MATCH with match_type 0. Exact match is non-negotiable for a key-based join - there is no legitimate use of approximate match on customer IDs. Normalise both key columns to text with TRIM in a helper column rather than relying on the cells' stored type.
- Add a permanent control block at the top of the pack: rows in source, rows matched, rows unmatched, sum of source vs sum of enriched, and a red/amber flag. A pack that cannot show this block should not be issued.
- Tell the CFO: the $12.84m total is right and the pack can go out with a corrected customer tab, but the tie-out was never evidence about the customer split. The three #N/A customers need to be resolved before the chart is published, and the top-20 repricing should be paused until the rates are re-derived.
- The habit to change: stop treating total-level agreement as a control. Controls live at the grain at which decisions are made, and the decision here is at the customer grain.