Forum Discussion
Bank recon matching
Happy New Year everyone! Can someone please assist me with this to a matching exercise for a bank reconciliation. Tried to do XLOOKUP with REGEXTEST but no luck. Below is the link to the excel fi...
Cleaner & faster
Add two helper columns in BOTH sheets:
Helper 1 – Normalized description
=LOWER(TRIM(SUBSTITUTE(C5,",","")))
Helper 2 – Signed amount
=IF(E5="DR",D5,-D5)
Then your match becomes trivial:
=XLOOKUP(G5,BANK!G:G,BANK!C:C,"",0)
This is fast, stable, auditable.
Excel Pattern (without helper columns)
Build a logical match using FILTER
In CBK!F5 (your “BANK Match” column), use:
=LET(amt,D5,txt,C5,dir,E5,FILTER(BANK!C:C,(BANK!D:D=amt)*(BANK!E:E<>dir)*REGEXTEST(BANK!C:C,TEXTJOIN("|",,TEXTSPLIT(txt," "))),""))
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.