Bank recon matching

Hi Tot86​,  Happy New Year!

I checked your sample file. The difficulty is that your bank descriptions often contain multiple transaction numbers (like "TRN 038,039,040..."), while the CBK sheet has cleaner references. XLOOKUP only works with exact matches, so you need to normalize the reference first.

Step 1: Helper Column Add a helper column to extract a clean transaction reference from the description. For example, if your description is in column C: =LEFT(TRIM(C2),8) This will return TRN 038 from "TRN 038,039,040...". If your CBK sheet uses TRN 038/21, adjust the length or strip the /21 with: =LEFT(C2,FIND("/",C2&"/")-1)

Step 2: XLOOKUP in Column F Now in column F, return the amount from the CBK sheet: =XLOOKUP(E2, CBK!A:A, CBK!B:B, "Not Found") E2 = the normalized transaction reference from your helper column. CBK!A:A = reference column in CBK sheet. CBK!B:B = amount column in CBK sheet. "Not Found" = shows if no match exists.

Step 3: Optional Amount Match If references don’t align, you can also match by amount: =XLOOKUP(D2, CBK!B:B, CBK!B:B, "Not Found") Here D2 is the bank amount.

Example For the row: DESC: TRN 102/21 F/THC (NZ) AMOUNT: 44,552.00 Helper column extracts TRN 102. XLOOKUP("TRN 102", CBK!A:A, CBK!B:B) returns 44,552.00. Column F shows the CBK amount.

So the final formula to paste in F2 is: =XLOOKUP(LEFT(TRIM(C2),8), CBK!A:A, CBK!B:B, "Not Found")

Copy it down the column, and you’ll have your reconciliation matches.

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.