Compare Two CSV Files: Matches, Differences, and Unmatched Rows

Comparing CSV files is not the same as reconciling them

A generic CSV diff tool can tell you that two files are not identical. Finance and operations teams usually need a different answer: which rows matched, which records are missing from either side, which rows have the same ID but a different amount or status, and which exceptions are real.

• Which rows matched?
• Which records are in File A but missing from File B?
• Which records are in File B but missing from File A?
• Which rows have the same ID but a different amount, status, fee, date, or reference?
• Which exceptions are caused by formatting, duplicates, or timing?

Example: two CSV files that almost match

File A might contain order IDs 1001, 1002, 1003, and 1004. File B might contain 1001, 1002, 1005, and 1004. A useful reconciliation shows 1001, 1002, and 1004 as matched keys, 1003 as missing from File B, and 1005 as missing from File A.

Method 1: compare CSV files in Excel with XLOOKUP

1. Open both CSV files in Excel.
2. Convert each file into a table.
3. Choose a stable key such as order ID, invoice number, transaction ID, or bank reference.
4. Add a helper column that looks for the key in the other file.
5. Filter blanks or errors to find missing records.
6. Repeat the lookup in the other direction.

=XLOOKUP([@order_id], FileB[order_id], FileB[payout_status], "Missing from File B")

=IF(ABS([@expected_amount]-[@actual_amount])<=0.01,"OK","Review")

Method 2: compare CSV files with Power Query

1. Import File A and File B.
2. Remove report headers, blank rows, totals, and extra columns.
3. Normalize text keys with trim, clean, or lowercase steps.
4. Convert dates and amounts into consistent types.
5. Merge File A to File B on the key column.
6. Use anti-joins or filtered nulls to identify unmatched rows.

Method 3: use FireLookup when the output needs to be reviewable

FireLookup is designed for reconciliation: upload Excel or CSV files, choose the matching workflow, and review matched and unmatched output instead of maintaining fragile helper columns.

• You need a repeatable matched and unmatched export.
• Non-technical reviewers need to understand the exception list.
• Rows have duplicate keys or one-to-many relationships.
• Exact matches are not enough because references, names, or descriptions are inconsistent.
• You want less workbook setup before every reconciliation cycle.

Common failure modes when comparing CSV files

• Same value, different format: dates, amount signs, spaces, casing, and text numbers.
• Duplicate keys: a lookup may return the first match and hide the rest.
• Gross amount vs net amount: payout reports may include fees, refunds, chargebacks, or reserves.
• Timing differences: a transaction may appear in one period on one side and the next period on the other.
• Text diff tools give noisy output instead of business exception buckets.

A simple CSV reconciliation checklist

• Each file has one header row and one record per row.
• Original exports are preserved before editing.
• The match key is agreed in advance.
• Dates, text, and amount signs are normalized.
• Duplicate keys are identified before lookup or merge.
• Amount comparison rules are documented.
• Matched and unmatched outputs are saved for review.
• Exceptions are categorized instead of left as one long list.

FAQ

Can Excel compare two CSV files?

Yes. Excel can compare two CSV files if you open or import them into workbooks, choose a stable match key, and use formulas such as XLOOKUP or COUNTIF. This works best when keys are unique and the files are clean.

What is the easiest way to find unmatched rows between two CSV files?

For small files, use XLOOKUP in both directions: File A to File B, then File B to File A. For reviewable reconciliation output, use a workflow that separates matched rows, rows missing from File A, and rows missing from File B.

Why do two CSV files show differences when the data looks the same?

Common causes include extra spaces, different date formats, amounts stored as text, reversed debit or credit signs, duplicate keys, hidden characters, and gross-vs-net amount differences.

Is a CSV diff tool enough for reconciliation?

Sometimes, but not always. CSV diff tools are good at showing file differences. Reconciliation usually needs business-key matching, duplicate handling, amount checks, exception categories, and matched/unmatched exports.

When should I stop using formulas for CSV reconciliation?

Move beyond formulas when the file is repeated often, row counts are growing, duplicates are common, grouped transactions are expected, or the reviewer needs a clean audit trail instead of helper columns.