Lookup data from another table when text exists

Afternoon,   I'm trying to work out how I can look up a value from Table A if the text in a column of Table B contains text in a different column in Table A.   To give an example of the data:   ...