A wrong reconciliation result is dangerous because it can still look finished: matched rows, tidy Excel tabs, and one final number that appears to tie.
That is why the first move is not to edit the formula, add a balancing line, or start scanning every row again.
The first move is to prove where the result stopped being trustworthy.
If a reconciliation was wrong, how to find where it broke is a breakpoint problem. You are looking for the exact point where trusted inputs turned into an unreliable output. That point might be the source file, the date range, the match key, the formula range, the duplicate handling, the amount sign, or the final report.
Treat the work like a controlled investigation. Freeze the current version. Rebuild the comparison boundary. Then test each layer in order.
Rebuild the boundary before touching the match logic
Do not start with the unmatched rows.
Unmatched rows are symptoms. They tell you the final comparison failed. They do not tell you whether the problem came from the source data, the scope, the import, the formula, or the report.
Start by writing down the boundary of the reconciliation:
- File A and file B being compared
- Period covered by each file
- Account, payout batch, client, entity, or ledger section in scope
- Last known good close, export, or reconciliation run
- Version of each source file used in the wrong result
- Filters, pivots, hidden rows, or manual edits applied before matching
Then run the integrity checks before changing anything.
| Check | File A | File B | What a break tells you |
|---|---|---|---|
| Row count | Count before filters | Count before filters | Rows were removed, added, or hidden before the match |
| Earliest date | First transaction date | First transaction date | The date range or cutoff may be wrong |
| Latest date | Last transaction date | Last transaction date | The period may include extra or missing activity |
| Total amount or net movement | Total debit/credit or signed amount | Total debit/credit or signed amount | The source total changed before row matching |
| Blank key count | Missing reference values | Missing reference values | The match key cannot support a full reconciliation |
| Duplicate key count | Repeated references | Repeated references | One-to-one matching may produce false results |
If one of these checks fails, stop there. The reconciliation did not break at the final formula. It broke before the comparison began.
For example, a bank CSV with 2,480 rows and a ledger export with 2,511 rows might be valid if one file contains opening balance lines or pending items. But if last month's same report had 2,480 ledger rows before a user filtered out voided entries, the row-count difference is evidence. The investigation moves to scope and filters before any row-by-row work.
Classify the failure early
Put the visible symptom into a class. This prevents you from treating every difference as the same kind of error.
| Symptom | Likely class | First check | Carry or correct |
|---|---|---|---|
| Balance was correct yesterday and wrong today | Edited source file | Compare file version, row count, and modified timestamp | Correct by rebuilding from a clean export |
| Difference equals one known transaction | Missing record | Search the transaction ID, then amount and nearby dates | Correct if it belongs in the period |
| Same difference appears every month | Timing or recurring posting error | Check prior carry-forward and opening balance | Carry timing; correct posting error |
| Books are higher than bank | Missing bank item, duplicate book item, or timing | Check uncleared deposits and duplicate ledger receipts | Carry timing; correct duplicate or missing entry |
| Bank is higher than books | Missing book receipt or wrong cutoff | Search deposits by amount and surrounding dates | Correct if omitted from books |
| Reconciliation report shows matched rows but wrong total | Formula range or sign logic | Check included rows, excluded rows, and debit/credit signs | Correct formula or mapping |
| One file has fewer rows after import | Scope or filter error | Remove filters and compare raw export row count | Correct scope before matching |
| References look identical but do not match | Key format error | Check spaces, leading zeroes, and text versus number format | Correct matching basis |
This table is not the answer. It is the triage step.
The goal is to stop guessing. A wrong date range is not solved by fixing VLOOKUP. A duplicate is not solved by widening the date tolerance. A timing item is not corrected in the same way as a missing ledger entry.
Find the last trusted state
A wrong reconciliation usually has a last point where the work was still reliable.
That point might be:
- The original bank export before anyone opened it in Excel
- The ledger export downloaded from the accounting system
- Last month's signed-off reconciliation
- The first version of the working spreadsheet
- A saved report before formulas were copied down
- A prior matched report with a known remaining difference
Find that state before you repair anything.
If last month's reconciliation was signed off with a zero unexplained difference, that close becomes the anchor. Your current work should start from the closing balance and unreconciled items carried forward from that point. If the opening balance no longer agrees, the current month's matching process is not the first problem. Something changed behind the starting line.
If the source CSV was edited during the process, treat it as a data-integrity issue. Excel can reinterpret dates, strip leading zeroes, convert long IDs, remove blank rows, or shift columns after a file is opened and saved. The safest recovery path is not to repair the edited file in place. Re-export the original source and compare it structurally against the edited version.
That is also the point to use a dedicated recovery workflow if the file changed mid-process: recover a reconciliation after the source CSV was edited.
The last trusted state gives you a control sample. Without it, every correction becomes another possible source of error.
Test scope before row matching
Scope errors are fast to miss because the file can look complete.
Check the period first:
- Does the bank statement run from the first to the last day of the month?
- Does the ledger export use posting date, transaction date, settlement date, or created date?
- Are pending transactions included in one file and excluded from the other?
- Were reversed, voided, or deleted entries included?
- Did one report include opening balances or summary lines?
- Did a filter remove zero-amount rows, fees, refunds, or adjustments?
Then check the account boundary:
- Is the bank account the same account used in the ledger?
- Are transfers between accounts included twice?
- Are card deposits grouped into a clearing account first?
- Did the export include all entities, locations, clients, or currencies?
Many wrong reconciliations come from a correct formula applied to the wrong scope.
Example:
| Source | Selected period | Amount total |
|---|---|---|
| Bank statement CSV | May 1-May 31 | 84,220.18 |
| Ledger export | May 1-May 30 | 81,640.18 |
| Difference | One missing day | 2,580.00 |
That is not a matching failure. It is a cutoff failure.
The fix is to rerun the ledger export for the correct period, then restart the match. Do not add a manual adjustment for 2,580.00 until you have proven the missing activity is not caused by the export boundary.
Compare the source files structurally
After scope, test whether the source files changed shape.
Look at column headers, not only row values. A formula can keep returning results after the export layout changes. Those results can be wrong because the formula is reading the wrong column.
Check:
- Header names
- Header order
- Hidden columns
- New columns inserted by the export
- Deleted columns
- Amount split into debit and credit instead of one signed amount
- Date field renamed or replaced
- Reference field moved or changed format
A common break looks like this:
| Expected column | Old position | New position | What breaks |
|---|---|---|---|
| Reference | Column C | Column D | Lookup uses the wrong key |
| Transaction date | Column A | Column A | Date still looks fine |
| Amount | Column E | Column F | Formula pulls fee or balance instead of amount |
| Status | Not present | Column E | Formula range shifts by one column |
The spreadsheet can look normal because the headers are still readable. The formula does not care. It follows ranges, positions, and named columns. If those changed, the output changed.
This is where XLOOKUP and VLOOKUP failures become misleading. A visible error such as #N/A is useful because it tells you the formula failed. A silent wrong match is worse. It gives you a number that looks finished.
To test the structure, compare a few known records manually:
- Pick one transaction that you know matched correctly before.
- Find its reference in both current source files.
- Confirm the amount, date, account, and sign.
- Confirm the formula is pulling those exact fields.
- Repeat for a high-value item, a refund or reversal, and a transaction near the period boundary.
If any known record pulls the wrong field, the reconciliation broke at mapping, not at matching.
Test the match key before the formula
The match key is the column or combination of columns that says two rows are the same event.
If the key is weak, the reconciliation can be wrong even when every formula works.
Strong keys include:
- Bank transaction ID
- Processor transaction ID
- Payout ID plus amount
- Invoice number plus customer
- Order ID plus gross amount
- Ledger reference plus posting date
Weak keys include:
- Description text alone
- Rounded amount alone
- Date alone
- Customer name alone
- Memo field copied from a bank feed
Before trusting the reconciliation, test the key itself.
| Test | What to check | Why it matters |
|---|---|---|
| Blank key count | Rows with no reference | Blank keys cannot prove a match |
| Duplicate key count | Same key appears more than once | One row can match the wrong twin |
| Format consistency | Text, number, spaces, leading zeroes | Identical-looking values may not be equal |
| Key stability | Same field exists in both exports | Export changes can remove the only reliable key |
| Collision risk | Same amount/date appears many times | The formula may match the first similar row |
If duplicates exist, do not pretend the key is unique. Add a second field to the match, such as amount, date, account, or transaction type. If the duplicate is real, the report should show it as duplicate risk or many-to-one activity, not hide it inside a forced one-to-one match.
This is also where a wrong reconciliation result can be traced without checking every row. Sort by the key. Count duplicates. Filter blanks. Check the high-value exceptions. A broken key usually leaves a pattern.
Separate timing items from errors
A timing item is not the same thing as an error.
Timing items are carried because the transaction belongs to the business activity but not to the same reporting point in both records. Errors are corrected because the record is wrong, missing, duplicated, or posted to the wrong place.
Use this split:
| Difference | Timing or error | Treatment |
|---|---|---|
| Deposit recorded in books on May 31, cleared bank on June 1 | Timing | Carry as deposit in transit |
| Check issued in May, not cleared by bank close | Timing | Carry as outstanding check |
| Stripe payout created May 31, landed June 2 | Timing | Carry or explain cutoff |
| Ledger entry posted twice | Error | Reverse or correct duplicate |
| Bank fee missing from books | Error | Record fee if it belongs in period |
| Transaction posted to wrong account | Error | Reclassify |
| Amount entered as 1,250 instead of 12,500 | Error | Correct amount |
| Source CSV edited and row removed | Data integrity error | Rebuild from clean export |
This distinction matters because a wrong fix can create a second error.
If you correct a timing item as if it were missing, next month's reconciliation will carry the damage forward. If you carry a real duplicate as timing, the books stay wrong.
When the difference is small and repeats every month, do not treat it as random noise. The repeated amount is evidence. It usually points to a recurring bank fee, rounding treatment, stale outstanding item, duplicated posting, or opening balance problem. For that specific pattern, use the workflow for a small unexplained bank reconciliation difference.
Re-run from a clean baseline
Once you know the break class, rebuild the reconciliation from the last trusted point.
Do it in this order:
- Re-export the source files if integrity is uncertain.
- Save untouched copies before opening them in Excel.
- Confirm row counts, date range, totals, blank keys, and duplicate keys.
- Confirm the headers and column mapping.
- Choose the match key and document any secondary key.
- Run the match.
- Classify unmatched items as timing, missing, duplicate, wrong amount, wrong date, wrong account, edited source, or unresolved.
- Produce the report from that classified result.
Do not repair the final output first. Repair the earliest broken layer.
If the date range was wrong, rerun the export. If the source file changed, recover the clean source. If the formula pulled the wrong column, fix mapping and rerun. If the match key was weak, change the key and rebuild the report. If the remaining items are timing, document them as timing instead of forcing them to tie.
The output should prove the breakpoint
The final report should not say only "matched" and "unmatched."
It should show where the reconciliation broke and what was done about it.
Use a structure like this:
| Section | What it proves |
|---|---|
| Source integrity | Which file versions, row counts, date ranges, and totals were used |
| Mapping | Which fields were compared and whether headers shifted |
| Match key | Which reference or field combination proved a match |
| Matched items | Rows that agree across both files |
| Timing items | Valid differences carried forward |
| Corrections | Missing, duplicate, wrong amount, wrong date, or wrong account items fixed |
| Unresolved items | Items still requiring review |
That output gives you control over the result. It also protects you from the worst kind of wrong reconciliation: one that looks tidy but cannot explain its own numbers.
The clean recovery workflow is simple to state, even when the work is detailed: freeze the wrong result, return to the last trusted state, prove the source files, prove the scope, prove the mapping, prove the key, separate timing from errors, then rerun the reconciliation from the earliest broken layer.
