Matching text string in a column/array to text in another column/array, return value

Hi all,

I'm looking to do the following using functions or a formula (se pic below): in table A I have text based information that includes text strings that I would like to match to text strings in another (reference) table (table B). Table B may be in another work sheet or a separate work book. I want to match the text strings in table A and also return the value of the match in table B into table A. It would be great to know how to do 1) an exact match an 2) a match where the text string in table B "contains" the text string in table A.

Any suggestions as to how I best accomplish this? I've played with Index/Match but can't get the combined functions to return the correct value.

Regards

Match.png

Update: see attached