Choose identity and row grain before reconciling exports
Separate a missing record, a split payment and a grouped amount difference before choosing a matching formula.
| Invoice | Source A | Source B | A minus B |
|---|---|---|---|
| SPLIT | 100 | 100 | 0 |
| ONLY-A | 7 | 0 | 7 |
| ONLY-B | 0 | 20 | -20 |
| ZERO-NET | 0 | 0 | 0 |
Define what one row means
An invoice may have several payment rows. Receipts of 40 and 60 against one invoice total 100; a first-match lookup returns only one receipt. Aggregate all relevant rows when the question asks how much was paid.
A membership test asks a different question: whether an ID occurs at least once. It does not establish the amount, transaction count or uniqueness of the observed records.
=SUMPRODUCT((D2:D11=A2)*E2:E11)Keep a deliberate identity policy
The course compares printable ASCII text IDs literally and ignores letter case. A* differs from AX, and 001 differs from 1. Leading and trailing spaces remain data. Cleanup or numeric conversion changes identity and should be a separate declared step.
Normalize case before taking unique keys. Use exact XMATCH mode for membership, or a literal equality mask for amounts. Criteria functions interpret wildcard characters unless the criterion is deliberately escaped.
Use the union when either side can be incomplete
A grouped reconciliation must include keys from both exports. A left-only lookup can hide every right-only key. Stack the ID lists, remove empty slots and deduplicate the normalized IDs before calculating each side independently.
Keep a zero-net key. Amounts of 10 and -10 sum to zero, but the source rows still exist. A missing-side total is numeric zero; it is different from a missing output cell.
=UNIQUE(FILTER(UPPER(VSTACK(A2:A9,D2:D11)),VSTACK(A2:A9,D2:D11)<>"",""))Read agreement at the level you checked
Totals of 100 on both sides give a zero grouped difference. They could represent one row of 100 on one side and two rows of 40 and 60 on the other. Group agreement does not prove transaction-level agreement.
Keep the original sources and both totals beside the residual. These bounded learning tasks use signed numeric cents and explicit empty-row rules. Native Excel/Sheets recalculation and edit/save/reopen remain separate checks.
Check the idea
One invoice has receipts of 40, 70 and -10. Which paid total belongs in its invoice report?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.