[EXCEL 365] Search Multiple Strings in String

[jimea]

Hello Guys,

maybe my problem is a bit more complex, but i want to solve it with formulas.

I just need a true or false as output.

At first =Find(range,string) doesnt work, i can just put one value into the searched value. If i try, i get false.
Example Table

A B
1 auto "auto likes flowers thats it"
2 flower "house"
3 house "bla auto blub"
4 "just something"

Now i want that the formula looks with the words from A in B for at least one hit. If not then false.
C1 - true
C2 - true
C3 - true
C4 - false


To use IF/OR/AND is not possible. Columne A will have 670 entrys to looking for, and B have 2100 string to be checked.

Maybe someone have a good idea to solve that.
=find($A$1:$A$3,C1) dont work, there is a false if i try. Same with search()

Have a nice week .

Greetings
Jimea

 

[Yongle]

Perhaps

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​				Formula
2​	auto	auto likes flowers thats it	TRUE​	=AND(A2>"",COUNTIF($B$2:$B$1000,"*"&A2&"*")>0)
3​	flower	house	TRUE​	=AND(A3>"",COUNTIF($B$2:$B$1000,"*"&A3&"*")>0)
4​	house	bla auto blub	TRUE​	=AND(A4>"",COUNTIF($B$2:$B$1000,"*"&A4&"*")>0)
5​		just something	FALSE​	=AND(A5>"",COUNTIF($B$2:$B$1000,"*"&A5&"*")>0)
6​

Sheet: Sheet8