using different sheets match string to specific work and enter that work in active
 

Excel 2010 VBA or Function solution

hi Guys

All below in the same workbook.
Starting from an empty column in a sheet called BankDownloadConvertor.
Positioning my cursor in Cell D2 which is titles Company use the text string in C2 ('CALL REF.NO. 0165 , PROSPECT CONSULT , FP 18/08/11 10 , 57173104389042000N)
then using a table “CompanyCategoryTrial” which is a list of all companies which does contain “Prospect Consultancy” locate/Match/Find “Prospect Consultancy” and enter it in cell D2.

Don’t mind how I achieve this as I have very little hair left trying various combinations. I am as you can deduce a beginner.

Here’s hoping I described my need well and you have a solution
All the best
Mark