How to reconcile expense claims with card statements
By the Analistable team · Updated · 6 min read
Match each card transaction to an expense claim on cardholder + amount + date (± a few days). Card transactions without a claim are missing receipts to chase; claims without a card transaction were paid personally (to reimburse) or are duplicates. Foreign-currency purchases need matching on the converted amount, with a small tolerance.
Part of our guide: How to reconcile data in Excel
Matching formula
=COUNTIFS(Claims[Employee], [@Cardholder],
Claims[Amount], ">=" & [@Amount] - 0.5, Claims[Amount], "<=" & [@Amount] + 0.5,
Claims[Date], ">=" & [@Date] - 3, Claims[Date], "<=" & [@Date] + 3)The ±0.50 tolerance absorbs currency rounding; tighten it for domestic cards. A count of 1 is a match; 0 means no claim; 2 or more needs a manual look.
Worked example
| Cardholder | Date | Merchant | Amount | Claim found | Action |
|---|---|---|---|---|---|
| A. Patel | 2026-09-04 | TRAINLINE | 86.4 | Yes | — |
| A. Patel | 2026-09-11 | HOTEL LYON | 142.37 | Yes (claimed 142.00) | Within tolerance (FX) |
| J. Okafor | 2026-09-18 | AMZN MKTP | 39.99 | No | Chase receipt |
| J. Okafor | — | (claim) Taxi | 24 | No card match | Paid personally — reimburse |
Things that break matching
- Merchant names on statements (“AMZN MKTP UK*2X”) differ from what people write on claims; match on amount and date, not merchant.
- Tips added after authorisation change the final amount.
- Refunds create negative card lines that should cancel an earlier claim.
- One claim for several card payments (a trip claimed as one line) — split the claim or match its total against the sum of the card lines.
Close the month
At month end, every card transaction should have a claim or an explanation, and the card statement total should equal the sum of matched claims plus the explained items. Keep the unmatched list and carry it forward.
The same date-and-amount matching underlies bank reconciliations.
Card transactions with no claim
Sort the unmatched card lines by age. Lines older than your submission deadline are policy breaches to escalate; newer ones are just claims not submitted yet. Send each cardholder their own list rather than one shared file.
Preparing the two files
- Card statement: one row per transaction with cardholder (or card number ending), transaction date, posting date, merchant and amount in your base currency. Corporate card portals export to CSV or Excel; column names vary by card provider.
- Expense claims: one row per claim line with employee, date of expense, category, description and amount. If your expense tool exports claims and receipts separately, use the line-level export.
- Use the same name or ID for each person on both sides. Card statements often show the name as embossed (“A PATEL”), so a small mapping table from card ending to employee ID is more reliable than names.
- Use the transaction date, not the posting date, when comparing with claim dates.
Second example: one claim for several card lines
A. Patel submits one claim of £212.40 for a trip. The card shows three separate transactions:
| Card date | Merchant | Amount | Trip ref |
|---|---|---|---|
| 2026-09-04 | TRAINLINE | 86.4 | TRIP-09 |
| 2026-09-05 | HOTEL BRISTOL | 98 | TRIP-09 |
| 2026-09-05 | RESTAURANT | 28 | TRIP-09 |
| Total | 212.4 |
None of the lines matches £212.40 on its own. Add a Trip ref (or Claim ID) column to the card lines, sum by it with =SUMIFS(Card[Amount], Card[Trip ref], [@[Claim ID]]), and compare the total with the claim. 86.40 + 98.00 + 28.00 = 212.40, so the claim is fully supported. Better still, ask the expense tool to export claims line by line so this step isn't needed.
The same match in SQL
When several months of statements are stacked, a query is easier to rerun than a sheet of COUNTIFS. This lists card lines with no claim from the same person within ±3 days and ±0.50:
SELECT c.*
FROM card c
WHERE NOT EXISTS (
SELECT 1 FROM claims k
WHERE k.employee_id = c.employee_id
AND ABS(k.amount - c.amount) <= 0.50
AND k.expense_date BETWEEN c.txn_date - INTERVAL 3 DAY AND c.txn_date + INTERVAL 3 DAY
)
ORDER BY c.employee_id, c.txn_date;The interval syntax shown is DuckDB's; other databases write date arithmetic differently. Swap the two tables to list claims marked as card spend that have no card line.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Most lines unmatched for one person | Name spelt differently on card and claims | Match on employee ID via the card-ending mapping |
| Foreign spend never matches | Claim entered in the original currency | Compare the claim's converted amount, or widen the tolerance for FX lines |
| Claim matched twice | Two card lines with the same amount and date | Mark each card line as used once matched |
| Refund line unmatched | Refund not linked to the original claim | Match negative card lines to the earlier claim and reduce it |
| Card total differs from the statement | Pending transactions in the export | Use posted transactions only |
Which method to use
For a handful of cardholders, the COUNTIFS sheet is fine and easy to hand to a manager. With dozens of cards or several months to catch up on, stack the monthly statements (see combining multiple Excel files automatically) and use Power Query or SQL so the match reruns with each new month. Whichever you use, finish by checking that matched plus unmatched lines add up to the statement total.
Doing it in Google Sheets
Import the card statement and the claims export onto two tabs. COUNTIFS accepts the same tolerance criteria, written with ranges, for example =COUNTIFS(Claims!B:B, A2, Claims!D:D, ">="&(D2-0.5), Claims!D:D, "<="&(D2+0.5)). Share a filtered view per cardholder rather than the whole sheet, so each person only sees their own transactions while chasing receipts.
Checking the result: matched card lines plus unmatched card lines equal the statement total; claims paid by card plus claims paid personally equal the claims total; and no card line is matched to more than one claim.
Common mistakes
- Matching on the posting date, which can be several days after the transaction and pushes lines outside the date window.
- Treating card lines with no claim as personal spend before asking the cardholder.
- Approving a claim for a personal payment that also appears on the company card, so the same expense is reimbursed and paid twice.
Repeating it each month
Keep a template with two empty tables, Card and Claims, and the matching columns already in place. Each month, paste the new statement and claims export, check the counts and totals at the top of each table against the source files, and filter for 0 and 2+ results. Carry forward unmatched lines with their original date so their age stays visible.
Frequently asked questions
- How do I match expense claims to card transactions?
- Match on cardholder, amount and a date window with COUNTIFS, allowing a small tolerance for foreign-currency conversions.
- What if the merchant name on the statement is different?
- Ignore the merchant name for matching; amount, date and cardholder are more reliable.
- How do I find duplicate expense claims?
- Count claims with the same employee, amount and date; more than one is a likely duplicate.
- Can I reconcile multi-currency card transactions?
- Yes. Match on the amount in the card's billing currency, and compare it with the claim converted at the transaction date's rate, allowing a small tolerance.
- How often should card reconciliations run?
- Monthly at least, ideally when each statement closes, so missing receipts are chased while people still remember the purchase.
- What if one claim covers several card transactions?
- Sum the card transactions for that cardholder and trip, and match the total to the claim line.