Reconcile Bank Statement Forumla

Hello All!

Looking for some assistance with a formula to reconcile bank data against my data (register). Sample file has some detail, but in a nutshell...Want to match date, transaction and amount and return an "R" if they match, "?" if they don't.

One catch is the dates could be slightly different, but the month and year should match 99.99% of the time. For example I enter 2/15/2021 in as the date, but the bank doesn't post it till 2/17/2021. Close but not a match.

The second catch is the transaction may not match exactly, but they will have matching words. I might call it Electric Bill, but the bank data shows it as Local Electric Company. So I'd like to be able say contains Electric as the match. Man this is complicated.

Trying to think through this it might be some like: IF Bank date is = to My Register date (+ or - 5 days) and Bank Transaction contains any matching text in My Register Transaction, and the Withdrawal or Deposit amount matches, then return "R" or if no match, return "?"

Man this is complicated. Might just have to do it manually if I can't get it sorted out. Using Excel as part of O365 and tinkering with Microsoft Money for Ecxel. Thanks in advance for any guidance.

Whh3

Hi
Since your reconciliation does not consist of accurate matching values - the automatic comparison will not be accurate as well, and to my opinion needs a human control and intervention.

I came up with the below formula that finds a match based on the text, month of the transaction, and amount.
However - this formula will put your "Water Co." transaction ,for example, in question mark though you marked it "R", since the "co." is not part of the text displayed in the bank. If you remove the dot "." then the formula will recognize it as a match...

=IF(SUM(ISNUMBER(SEARCH(B4,$J$4:$J$13))*(C4=$K$4:$K$13)*(D4=$L$4:$L$13)*(MONTH(A4)=MONTH($I$4:$I$13)))>0,"R","?")

Wow! Thank you all for the quick responses! Give me some time to play with the different options (my day job gets in the way!), then I'll mark this as solved. I do appreciate the help figuring this stuff out.

I've been playing with the different formula, and learning along the way. Looking at Bebo021999's formula and seems real close, but I'm not getting the expected results.
Almost the reverse. Can you all look at sample2 and see if there is anything obvious I'm missing in the formula. Again...appreciate everyone's help!

JohnT: I see you are using two columns...I didn't think about that. =IF([@Withdrawal]="","",IF(IFERROR(MATCH([@Withdrawal],K3:K12,0),""),"R","?")) This doesn't seem to take in account matching the transaction name. My thinking was match name, date and amount so that if there is over lap month-to-month (recurring bills) it would know which one to reconcile. I'd be interested in your macro code if you're willing to share. Could you use it in Sample2, which is closer to my real data?

Bebo: Seems to be working with =IF(SUMPRODUCT((Transactions!$B$2:$B$8=K2)*(ABS(Transactions!$A$2:$A$8-C2)<=5)*(Transactions!$D$2:$D$8))=H2+I2,"R","?"). So the answer was order of the calculation...meaning I need to go to the transactions tab first. Is that right?

Thanks everyone. Close to marking "resolved."

I'm having the same problem as him:-
(i) The dates and (ii) Names in bank statement different than cash book (iii) cash book shows 008024 while bank shows #8024.
Meanwhile thanks for the solution, i believe in power query there is a way using append to simplify this.