Forum Discussion

Tot86's avatar
Tot86
Copper Contributor
Jan 06, 2026

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 file.

https://1drv.ms/x/c/80b0e5c8d40a83ef/IQAn2IxiOBDpSJBIIZCfPj3TAexy2EEPYT5VjpDkcpTzpB4?e=S4NEuo

 

excel
Formulas and Functions

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 09, 2026

    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.

Resources