Try:

=IFERROR(LOOKUP(2,1/SEARCH($B$1:$B$5,A1),$B$1:$B$5),"No")

copied down after adjusting range to suit. Don't use whole columns, use only range containing data.

Brilliant, this worked perfectly.

Any chance you can break it down so I know why it worked ha

Thanks very much for the help!