IF range of cells contains string of text, return contents of cell where string is found

Just as title says:

IF range of cells contains string of text, return contents of cell where string is found

For example

Col A

dog
cat


Col B

the dog ran
the (cat) walked
the (dog) ran
the cat walked

So a formula that would search for the contents in A1 and return the entire cell B1

HI
i cell D1 write your Criteria
for example dog

in cell E1 use this CSE formula

Please Login or Register  to view this content.

the press Ctr+Shift+Enter

copy it down

Definitely there are better solutions. Try this formula.

= IF(OR(IFERROR(SEARCH($A$1,B1),-1)>0,IFERROR(SEARCH($A$2,B1),-1)>0),B1,"")

See attach file.

b1-per-a1.xlsx

Originally Posted by Yahya

HI
i cell D1 write your Criteria
for example dog

in cell E1 use this CSE formula

Please Login or Register  to view this content.

the press Ctr+Shift+Enter

copy it down

Ive had the best luck with this formula so far but still doesn't return exactly what I want.

Does it make a difference if my criteria is on one sheet and what I'm searching through is about 450 rows on another sheet? And then returning the cell where it found my criteria in the sheet with where the criteria is located.

Perhaps

=VLOOKUP("*"&A1&"*",B:B,1,FALSE)

Try

=VLOOKUP("* "&A1&" *"," "&B:B&" ",1,FALSE)

IMPORTANT,
This is an array formula now, and must be entered with CTRL + SHIFT + ENTER

Originally Posted by Jonmo1

Try

=VLOOKUP("* "&A1&" *"," "&B:B&" ",1,FALSE)

IMPORTANT,
This is an array formula now, and must be entered with CTRL + SHIFT + ENTER

Works perfectly! Thanks a lot!

You're welcome, thanks for the feedback.