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

Originally Posted by crazyb78

I unfortunately don't want to sum them but it will be useful later on in my project so thank you.

Or

In E2 Cell


=IFERROR(LOOKUP(2,1/SEARCH(LEFT(D2,FIND(" ",D2&" ")-1),$B$2:$B$8),$C$2:$C$8),"")


Drag it down


But don't expect accurate result. When there is no reliable search text then the result will also won't be a reliable one