How to Match Credit Card Transactions and Purchases by Date and Amount in Excel
Question details
The user needs a method to compare transactions across two Excel worksheets where purchase amounts match exactly, but the transaction dates might differ by a one-day variance.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Reconciling credit card statements with e-commerce purchase histories (like Amazon) where processing dates may lag.
- Observed behavior
- Looking for an automated formula to accurately flag matching records based on exact amounts and a flexible date range (+/- 1 day) without manual line-by-line checking.
Ensure both your credit card statement and Amazon purchase history are imported into separate sheets within the same workbook, with dates correctly formatted as date values rather than text.
Use SUMPRODUCT and Date Range Helper Columns
Create minimum and maximum date helper columns to easily match transactions allowing a 1-day variance using a SUMPRODUCT formula.
This method uses the SUMPRODUCT function to evaluate multiple criteria simultaneously: exact amount matches and a permitted date window. You do not need an explicit key column for this to work.
Open the sheet containing your credit card transactions (e.g., Sheet1). Insert two helper columns. In column C (Min Date), enter `=A2-1`. In column D (Max Date), enter `=A2+1` (assuming the original transaction date is in column A).
In a new column to check for a match, enter the formula: `=IF(SUMPRODUCT((Sheet2!B$2:B$1000=Sheet1!B2)*(Sheet2!C$2:C$1000>=Sheet1!C2)*(Sheet2!C$2:C$1000<=Sheet1!D2))>0,"YES","NO")`.
Press Enter, then click and drag the fill handle down to apply the formula to all your transaction rows. A 'YES' output indicates a matching transaction was found in Sheet2.
Compare Monthly Totals using a Pivot Table
If you don't need line-by-line reconciliation, a Pivot Table can quickly help you compare monthly aggregates between the two sheets.
Easily Match and Reconcile Transactions in WPS Spreadsheet
WPS Spreadsheet provides powerful functions like SUMPRODUCT and Pivot Tables to help you reconcile bank statements and purchase histories effortlessly. It handles complex data analysis with ease.
- 1. Download and Install: Download WPS Office and open your financial workbook in WPS Spreadsheet.
- 2. Apply Helper Columns: Set up your minimum and maximum date variance columns directly in your transaction sheet.
- 3. Insert Formula: Use the SUMPRODUCT formula provided above to automatically flag matched transactions.
Frequently Asked Questions
Why does the SUMPRODUCT formula return a #VALUE error?
This typically happens if the ranges in your formula are not the exact same size (e.g., comparing B2:B100 to C2:C1000) or if there is text in the date or amount columns. Ensure all ranges match and columns are strictly formatted as numbers or dates.
Can I change the date variance from 1 day to 3 days?
Yes, you can easily adjust the helper columns. Instead of using `=A2-1` and `=A2+1`, simply use `=A2-3` and `=A2+3` to allow a broader 3-day window for matching transactions.
Do I need a helper key column for this matching formula?
No, a unique helper key is not required when using the SUMPRODUCT method. The formula evaluates the date range and the exact amount directly across the two data arrays.
Is there a way to highlight matched transactions instead of showing YES or NO?
Yes. You can use the core logic within Conditional Formatting. Select your rows, go to Home > Conditional Formatting > New Rule, choose to use a formula, and enter `=SUMPRODUCT(...)>0` to apply a background color to matching rows.
