Search Bank Statement Lines for Partial Strings Stored in Reference Table

Hi All - newbie here so please be kind!

I have a problem I am trying to solve in vba, but I need help as I'm not even sure how to do it in Excel using regular formulae so I can't even record on screen steps to get a clue!

I have two Worksheets:

1. cumulative bank statement with the following fields [date, line description, payment type, credit amount, debit amount]
2. reference table containing unique partial strings from regularly occurring transactions and against each string a code

What I want to do when a new bank statement is added, is perform a search of all the unique partial strings from my reference table within each of the new line description fields, and if there is a match, return the code from the reference table corresponding to unique partial string that was found. The idea being to speedily code the regular/recurring transactions (rent, rates, gas, elec, etc) and just leave the others that need manually reviewing and coding.

I think this should be possible with some sort of array formula but I'm stumped, so all help gratefully received.

This is an example of a couple of line descriptions:
DIRECT DEBIT PAYMENT TO UNITED UTIL WATER REF ##########, MANDATE NO 0009
INTEREST PAID AFTER TAX 0.00 DEDUCTED

and extract from reference table:
Unique String Code
OVO ENERGY 22: Util_Gas/Elec/Water/Insur
UNITED UTIL WATER 22: Util_Gas/Elec/Water/Insur
HML T/A AVIVA 22: Util_Gas/Elec/Water/Insur
Cashback 02: Interest/Fees
INTEREST 02: Interest/Fees
MONTHLY FEE 02: Interest/Fees
EE LIMITED 23: Util_Mobiles

So in the two line descriptions I want to put "22: Util_Gas/Elec/Water/Insur" against the first and "02: Interest/Fees" against the second.

Hope that all makes sense.

I'm not sure about a formula but a macro could what you want. If you are interested in a macro, it would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

Glad it worked out.