Money basics

Can you auto-match receipts to bank transactions? A forum answer

A forum-style answer on how to auto-match receipts to bank transactions in India with simple rules and a spreadsheet formula, plus the honest limits.

Editorial illustration for Can you auto-match receipts to bank transactions? A forum answer

The question: "I match every receipt to my bank statement by hand, one line at a time, and it takes hours. Is there a way to auto-match receipts to bank transactions?" The honest answer is that you can automate the easy matches with a rule and a formula, and then handle the awkward rest by eye. Here is how we would set it up.

Decide what counts as a match

A receipt and a transaction match when three things agree: the amount, the date within a short window, and the merchant if you can tell. Write that rule down before building anything, because every tool or formula only applies a rule you already hold in your head.

The steps

  1. Make a receipts list with one row per bill: date in column A, shop in column B, amount in column C.
  2. Paste your statement rows into a second sheet with the same columns: date in A, narration in B, amount in C.
  3. In the receipts sheet, add a column D that counts statement rows with the same amount within two days.
  4. Filter column D for zero and for more than one. Zero means unmatched. More than one means ambiguous.
  5. Resolve the unmatched and ambiguous rows by hand, using the narration and the time on the bill.

The formula that does the counting

In Excel or Google Sheets, put this in D2 of the receipts sheet, with the statement on a sheet named Statement and dates stored as real dates:

=COUNTIFS(Statement!$C$2:$C$500, C2, Statement!$A$2:$A$500, ">="&A2, Statement!$A$2:$A$500, "<="&A2+2)

Read it as: count statement rows whose amount equals this receipt's amount, whose date is on or after the receipt date, and whose date is no later than two days after it. Copy it down the column. Amounts must be stored as numbers, not text with rupee signs or commas inside quotes, or the count will be zero for everything.

A worked example with made-up bills

Say you have six receipts and the formula returns these counts.

ReceiptAmountCountWhat I do
Pharmacy₹1,2601Matched
Grocery₹5,2361Matched
Fuel₹2,0002Check narration, two fuel entries
Cafe₹4800Paid in cash, add by hand
Hardware₹1,9801Matched
Stationery₹5121Matched

Four matched at once, one ambiguous, one unmatched. So two of six need a human, and those are exactly the ones a person should look at. The ambiguous fuel row is two ₹2,000 entries on nearby days; the narration or the time on the bill settles it.

What can go wrong with auto-matching?

  • A round amount like ₹2,000 or ₹500 appears many times a month, so it matches wrongly. Use the narration or time as a second check.
  • The bank posts the transaction a day or two after the bill date. That is why the window exists.
  • A tip, round-off or service charge makes the bill and the statement differ. These will show as unmatched and need a look.
  • One bill covers a payment split across two cards or two UPI payments, so the amounts will never agree.

Where an app fits, and where it does not

If you are doing this for hundreds of rows a month, a sheet gets slow. SpendixAI imports statements from PDF, CSV or Excel, lets you review rows before saving, and keeps receipts with their transactions. Be clear about the limit: it does not read your bank account or your SMS, and it does not promise a perfect match, so you review the pairing yourself. The sheet method above works fine without any product.

A closing checklist

  • Write down your match rule: amount, date window, merchant.
  • Keep amounts as numbers and dates as real dates.
  • Filter for zero and for more than one.
  • Resolve those two groups by hand.
  • Save the result, so next month starts with only the new rows.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports