Two bank statement CSVs can look close enough to compare in Excel, but that is usually why the mismatch gets worse. One file stores debits as negative amounts. The other splits them into separate debit and credit columns. One uses the posting date. The other uses the value date. By the time you start building lookups, the files are already disagreeing about what a single transaction is.
The safest way to compare two bank statement CSV files without formulas is to treat both exports as raw transaction files, map each column by role, match rows by the strongest shared key, and review only the exceptions that remain.
Read the mismatch before you touch the files
Most false mismatches show up before any comparison starts. The two exports are already storing the same transaction differently.
Use this table as the first pass.
| What you see on screen | How File A stores it | How File B stores it | Result when matched |
|---|---|---|---|
05/01/2026 and -125.40 | Date is text 05/01/2026; amount is one signed value | Date is a real date serial; amount is 125.40 in a Debit column | Match fails on both date type and sign convention |
1,250.00 in both files | Amount is text with a comma and hidden leading apostrophe | Amount is a real number | Values look identical but a spreadsheet lookup treats them differently |
TRANSFER TO SAVINGS appears twice for 250.00 | Two separate rows share the same date and amount | One row includes a bank reference, the other does not | Date-and-amount matching creates duplicate candidates |
AMAZON vs AMAZON MKTPLACE PMTS | Description is truncated by one bank export | Description keeps the longer merchant text | Description-only matching fails even though the payment is the same |
31/03/2026 in one file and 01/04/2026 in the other | File uses transaction or swipe date | File uses posting or settlement date | Same movement is reported as a timing difference, not a missing row |
Opening Balance exists only in one file | File includes statement control rows in the CSV | File contains transactions only | That line should not be matched as a transaction at all |
If two cells still look suspiciously identical after this first pass, use =TYPE(A2) as a spot check. 1 means Excel stored a number. 2 means it stored text. If Excel keeps returning #N/A on values that appear to match, why VLOOKUP returns #N/A on mismatched CSV formats is usually the underlying failure.
Pick the column that deserves to be the match key
The whole comparison gets easier once you stop asking every column to do the same job. Some fields are safe for matching. Some are only supporting evidence.
| Candidate key | Use it when | What it does well | Where it breaks |
|---|---|---|---|
| Bank reference or transaction ID | Both exports preserve a bank-generated reference | Best primary key for true one-to-one matching | Many CSV exports omit it or rename it |
| Posted date + signed amount | Both files use the same date basis and same sign rule | Works for clean same-account comparisons | Breaks on one-day shifts and repeated same-amount rows |
| Amount + reference fragment | One file truncates descriptions but preserves part of the reference | Useful when bank reference is partial but consistent | Weak for card traffic and recurring merchant payments |
| Description + amount | Reference is missing and merchant text is stable | Good as a fallback review key | Breaks on abbreviations, truncation, and duplicate same-day purchases |
| Running balance | You need supporting evidence after a likely match | Helps confirm row order around a disputed transaction | Never use it as the primary key because one inserted row shifts everything after it |
Amount alone is weak. Two card purchases for 19.99 are common. Description alone is weak because one bank might export UBER BV while another writes UBER *TRIP. Running balance is useful for context, but it is not stable enough to drive the whole match.
A clean comparison usually uses a hierarchy:
- Match by bank reference when both files have it.
- Fall back to signed amount plus date when reference is missing.
- Use description only to review leftovers, not to drive the first pass.
Map the columns by role, not by header name
Two bank CSVs often describe the same field with different headers. The fix is not to rename every column manually. The fix is to decide what job each column plays in the comparison.
Here is a typical mapping:
| Comparison role | File A header | File B header | Rule |
|---|---|---|---|
| Transaction date | Posted Date | Value Date | Treat as comparable only after you know whether the banks use the same date basis |
| Amount | Amount | Debit and Credit | Convert to one signed amount rule, or compare debits and credits in separate passes |
| Reference | Reference | Bank Ref | Use as primary key if both sides preserve it |
| Description | Description | Narration | Use to support review, not as the first key |
| Balance | Balance | Running Balance | Keep for diagnostics only |
| Transaction type | Type | Details | Useful for excluding fees, reversals, or control rows |
If one CSV uses separate debit and credit columns, do not force that file into a bad single-column comparison by eyeballing signs. Either use a comparison tool that lets you map debit as negative and credit as positive, or run two passes: debits against debits, then credits against credits. That is still cleaner than burying the sign logic inside lookup columns that no one wants to audit later.
Build the comparison in passes, not in one giant match
Pass 1: lock the raw files
Keep the original exports untouched. Do not overwrite them, sort them in place, or delete rows before you know what those rows mean. If one file later turns out to contain opening balance lines or pending card transactions, you need the untouched version to prove it.
Pass 2: exclude rows that are not real transaction matches
Before matching anything, identify rows such as:
- Opening balance
- Closing balance
- Statement summary rows
- Blank separator rows
- Export footer text
Those lines belong in period control, not transaction matching. If one file includes them and the other does not, you are manufacturing discrepancies before the work starts.
Pass 3: run the strongest key first
Start with the highest-confidence rule available.
| First-pass rule | Best use case | Expected outcome |
|---|---|---|
| Reference + amount | Same account exported from two systems | Most rows match cleanly with low review effort |
| Signed amount + posted date | Reference missing but date basis is aligned | Good coverage on simple bank exports |
| Debit-only or credit-only pass | One file splits amounts across two columns | Prevents false sign mismatches |
Do not bring description into this first pass unless you have to. Merchant text creates noise early and rarely solves it.
Pass 4: review the leftovers by exception type
After the first pass, every remaining row should go into one of a few clear buckets:
| Exception bucket | What it usually means | What to do next |
|---|---|---|
| Missing from File A | Transaction exists only in File B | Check date filter, statement period, or whether File A dropped that transaction type |
| Missing from File B | Transaction exists only in File A | Check export completeness, pending items, or duplicates |
| Same amount, different date | Posting delay or value-date difference | Mark as timing unless another field disagrees |
| Same date, same amount, multiple candidates | Duplicate-value problem | Bring in reference, balance context, or nearby sequence |
| Same row, different sign | Debit/credit convention mismatch | Rework amount rule before investigating further |
| Same text, different stored value type | Text-vs-number issue | Normalize the field type, then rerun the comparison |
Pass 5: use description only to settle ambiguous leftovers
Once the exceptions are down to a small set, description becomes helpful. At that stage you are no longer scanning thousands of lines. You are deciding whether ATM WDL 004281 is the same cash withdrawal as ATM CASH 4281.
Description is a review tool. It is not the backbone of the comparison.
Handle the bank-statement edge cases that generic CSV guides skip
Generic CSV comparison pages usually stop at "upload two files and compare." Bank statements need more than that.
Posted date versus value date
Banks do not always agree on which date belongs in the export. A card payment made late in the evening can show one day earlier in one file and one day later in another. That is not a missing transaction. It is a timing difference. Treat it as its own bucket.
Split debit and credit columns
One file may store withdrawals in Debit and deposits in Credit, while the other uses one signed Amount column. If you do not normalize that rule first, every outgoing payment becomes a false mismatch.
Repeated same-amount transactions
Coffee shops, subscription charges, cash withdrawals, and transfer sweeps produce duplicates constantly. If you match only on date and amount, the wrong row can appear to reconcile while the real mismatch stays hidden.
Balance lines and summary rows
Some exports include opening balance, closing balance, or daily total lines inside the CSV. Those rows are useful for control, but they are not individual transactions and should never compete for a match key.
Filtered date ranges
One CSV may cover statement dates while the other covers download dates. That is how you end up comparing March 1 to March 31 on one side against March 2 to April 1 on the other. The files are not wrong. The extraction window is different.
Truncated descriptions
One bank export may cut merchant text after 18 characters. Another may keep the full string. Treat description as supporting evidence only, especially on card traffic.
A file comparison process can classify these cases cleanly. A large spreadsheet full of VLOOKUPs usually hides them inside helper columns and manual filters.
The comparison is finished when the output can answer a real finance question
Your final output should be able to show:
| Output section | What it should tell you |
|---|---|
| Matched transactions | Which rows are confirmed across both files |
| Missing rows | Which transactions exist on only one side |
| Timing differences | Which rows agree in amount but sit on different dates |
| Duplicate candidates | Which same-value rows need a stronger key |
| Control totals | Whether unmatched amounts are explained or still open |
If the comparison feeds month-end work, the next question is usually not about the CSV anymore. It is about why the bank side and the books still disagree after the statement match is done. That is where what to do when books do not match your bank at month end becomes the next diagnostic step.
When the monthly rerun is the real waste
The manual pain is rarely the first comparison. It is the fifth one. Then the tenth. Then the version where one bank changes the headers, another adds a balance column, and the statement deadline does not move.
At that point, the work is no longer analytical. It is repeated file matching, repeated exception review, and repeated report cleanup.
