match long string with spaces against smaller string and return label

help...

I've tried index, match, vlookup, sumproduct etc and cant work out how to check the following bank transactions...

'SANTANDERCARDS LTD **transaction 1
0207 28AUG19 , RINGGO , RICHMOND GB
'HALIFAX
'nuffield health
'0207 25AUG19 , RINGOPARKBYPHONE , SOUTH WEST TR GB
'TV LICENCE MBP
'EE LIMITED
'LBRUT DIRECT DEBIT
'BARCLAYS UK MTGES
'BARCLAYS UK MTGES

...against the below list of references and return column B value (e.g. credit card bill, gym, parking) if there is a match.

col A col B
SANTANDER Credit Card bill
nuffield Gym
RINGGO Parking

i.e. check transaction 1 against the list and return "credit card bill", then perform the same for the rest of the transactions

Thanks

Hi, uploaded,

I want to read down the transactions in the 'current*' sheet (column I) and xref to the data in sheet 'ref' and return the value from column B
if there is a match...

Thanks.

Try this

Please Login or Register  to view this content.

Hi

I suppose you should use another approach instead of the one you are using.

If I understand your question, do this:

In ref!F1 use (and copy down)

Formula:

Please Login or Register  to view this content.

In ref!G1 use (and copy down)

Formula:

Please Login or Register  to view this content.

In 'Current33035547-20190903 - upd2'!I4 use (and copy down)

Formula:

Please Login or Register  to view this content.

See the file in a new format xlsx

Hi
I improved the solution proposal from the previous post and I implemented four solutions. The latest implementation is because it uses summer 2000 which does not support aggregate functions, the formula must be entered with CTRL+SHIFT+ENTER. See and choose the one that suits you best.

Notes: By necessity, on sheet 'ref' I inserted a starting row, put in B1 a blank space and in column A, since A2:A40 I replaced empty cells with a symbol not used in the description of bank movement.
See the file

Hi all, I used huuthang_bd's formula and it worked!

Thanks v much :-)

PS - thanks also Jose, I'll keep that one for another day.