If you work in finance or operations, you've almost certainly done this: two spreadsheets open side by side, a VLOOKUP in a spare column, scrolling through hundreds of rows trying to work out why one file says one thing and the other says something else. It works, but it's slow, fragile, and easy to get wrong.

This guide walks through what reconciling two CSV files actually involves, why the spreadsheet-formula approach causes so much friction, and a cleaner way to do it that takes seconds instead of an afternoon.

What "reconciling two files" really means

Reconciliation is the process of comparing two sets of records that should agree, and finding every place where they don't. In practice you're usually answering four questions:

• What appears in both files? These are your matched records — the happy path.
• What's only in the first file? Records the second system never received, or hasn't processed yet.
• What's only in the second file? The reverse — things the first file is missing.
• Where do the amounts disagree? Records that exist in both, but with different values.

The key that links the two files together is usually a shared reference — a payment reference, invoice number, transaction ID, or order number. As long as both files contain that reference in some column, they can be matched.

Why the VLOOKUP approach is painful

The classic method is to add a VLOOKUP (or XLOOKUP, or an INDEX/MATCH) that looks up each reference from file A inside file B. It works, but it has real weaknesses:

• It only looks one direction at a time. A lookup from A into B tells you what's missing from B, but you need a second lookup the other way to find what's missing from A.
• It breaks on formatting differences. If one file stores the reference as 00123 and the other as 123, or one has a trailing space, the lookup returns #N/A even though they're the "same" reference.
• It struggles with duplicates. When a reference appears on several rows — for example one payment split across multiple invoice lines — a plain lookup only finds the first match and silently ignores the rest.
• It doesn't sum. To compare amounts when a reference appears multiple times, you need SUMIF on top of the lookup, which is another layer of formula to get right.

None of these are insurmountable, but together they mean a "quick reconciliation" turns into an hour of formula-wrangling, and any mistake is invisible until someone notices the numbers are off.

A cleaner step-by-step approach

Whether you use a dedicated tool or build it carefully in Excel, a reliable reconciliation follows the same logical steps:

1. Identify the shared key. Find the column in each file that holds the same reference. It might be called something different in each — Payment Ref in one, Description in the other — but the values should match.
2. Normalise the values. Trim leading and trailing spaces, and decide whether case matters. ABC-123 and abc-123 should usually be treated as the same reference.
3. Group by the key. If a reference can appear more than once, group all rows that share it so you can compare totals rather than individual lines.
4. Match across both directions. Find references in both files (matched), references only in the first, and references only in the second — all in one pass.
5. Compare the amounts. For matched references, sum the amount on each side and flag any where the totals differ.
6. Review the exceptions. The matched rows usually need no attention. Your time goes into the unmatched and mismatched records — that's where the real work is.

Handling the tricky cases

Duplicate references

A single payout of £300 might correspond to three separate invoice lines of £100 each in your accounting system. That's not an error — it's a one-to-many relationship. The correct way to handle it is to sum all rows sharing a reference on each side and compare the totals: £300 against £100 + £100 + £100. They reconcile.

Formatting mismatches

Numbers stored as text, leading zeros, trailing spaces, and inconsistent capitalisation are the most common reasons a reconciliation "fails" when the data is actually fine. Always normalise before matching.

Amount differences that aren't errors

If your files involve foreign currency, two systems may convert the same payment at slightly different exchange rates, producing a difference of a few pence. These aren't genuine discrepancies. Setting a small tolerance — both an absolute amount and a percentage — lets you ignore this noise while still catching real problems. We cover this in detail in our guide to foreign currency reconciliation.

The bottom line

Reconciling two files isn't conceptually hard, but doing it well by hand requires juggling lookups, sums, normalisation, and duplicate handling all at once — which is exactly why it's so error-prone. Whether you build it carefully in a spreadsheet or use a purpose-built tool, the principles are the same: normalise your keys, match in both directions, sum before you compare, and focus your attention on the exceptions rather than the matches.