Given If any words in column A match any string of text in column B, return column B

Welcome to the forum.

Try this:

=IFERROR(LOOKUP(99999,SEARCH(B$1:B$4,A1),B$1:B$4),"")

Please try in C1 and copy down (confirm the formula with CTRL+SHIFT+ENTER):

Formula:

Please Login or Register  to view this content.

It does work.

my 3 cents with Power Query

Column1	Column1		Custom	Custom.1
cat tree apple	house		earth venus mars moon	venus
dog blue kid	cat		Post a small Excel sheet not picture showing realistic & representative sample data	picture & data
plant tree house	fly		house tree plant	house
red orange	venus		bird owl fly	fly
yellow fish mom	and		cat tree apple	cat
crazy dog jumping & barking	data		List.Generate() and Looping in PowerQuery	and
bird owl fly	picture		crazy dog jumping & barking	&
screen mouse	&		dog blue kid
table chair lamp			yellow fish mom
earth venus mars moon			red orange
List.Generate() and Looping in PowerQuery			screen mouse
Post a small Excel sheet not a picture showing realistic & representative sample data			table chair lamp

Please Login or Register  to view this content.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Thanks for the rep and for confirming that the formula I suggested does work.

You are welcome, thanks for the rep with feedback .