Spreadsheet matching for accountants and bookkeepers
By the Analistable team · Updated · 3 min read
Most of an accountant's or bookkeeper's spreadsheet time goes on matching one export against another: bank against ledger, invoices against payments, VAT return against sales, supplier statement against purchase ledger. Each job is a join on a reference, date or amount. This page lists those jobs, the key each one uses, and where to start.
The multi-sheet jobs that fill the month-end
Whether you keep the books for small clients or close the month for one company, the work is rarely about a single spreadsheet. It is about proving that two systems agree, and listing the rows where they do not.
| Job | Files | Join key |
|---|---|---|
| Bank reconciliation | Bank statement CSV + cash book / GL export | Date + amount, or payment reference |
| Debtor matching | Sales invoice list + payments received | Invoice number |
| VAT check | VAT return workings + sales and purchase ledgers | Period + tax code |
| Supplier statements | Supplier statement + purchase ledger | Supplier invoice number |
| Payroll check | Payroll report + timesheets or HR list | Employee ID |
| Card spend | Card statement + expense claims | Date + amount + cardholder |
The method behind every row of that table is the same: clean the key, join, then filter the unmatched rows on each side. The reconcile data in Excel guide walks through it once; the articles below apply it to each job.
Where formulas get painful
- Bank lines carry no invoice number, so you match on date and amount and have to handle two payments of the same value on one day.
- Exports from accounting software store references as text in one file and numbers in the other, so lookups fail silently.
- Client files arrive in different layouts each month, which breaks a reconciliation workbook built for last month's columns.
With Analistable you load both exports and ask, for example, "which bank payments have no matching ledger entry within three days?" The AI writes a read-only SQL join, shows you the query, and returns the unmatched rows. Your source files are not changed.
Client data stays on your machine: files are loaded into a database inside your browser and are not uploaded or stored on our servers. Only column names (and samples, if you allow them) are sent to the AI.
Start here
- Bank reconciliation in Excel
- Bank reconciliation template
- Match Stripe payments to invoices
- Reconcile a VAT return with the sales ledger
- Vendor statement reconciliation template
- Reconcile expense reports with card statements
- Find vendors billed twice
- Find gaps in invoice numbers across files
- Numbers stored as text stop lookups working
Frequently asked questions
- Does Analistable replace my accounting software?
- No. It reads exports from your accounting, banking and payroll systems and answers questions across them. It never posts journals or writes back to the ledger.
- Can I match bank lines that have no invoice reference?
- Yes. Ask for a match on amount and a date window, for example within three days. Review duplicates of the same amount by hand, as you would in Excel.
- Is it suitable for client data?
- Rows are processed in your browser and are not stored on our servers. Check your own engagement terms, but the data does not leave your device as a file upload.
- I'm a bookkeeper with small clients. Is this overkill?
- For one bank account and a short invoice list, a template or a lookup formula is enough. Analistable helps when files are long, layouts change, or you need several joins.