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