Return value when part of a cell matches a word in a reference column

OR,

If you just want to pull the first matching value from column C into E, try the following formula in E2:

=IFERROR(INDEX(C$2:C$8,MATCH("*"&D2&"*",B$2:B$8,0)),"")