[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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]auto[/td][td]auto likes flowers thats it[/td][td]

TRUE​

[/td][td] =AND(A2>"",COUNTIF($B$2:$B$1000,"*"&A2&"*")>0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]flower[/td][td]house[/td][td]

TRUE​

[/td][td] =AND(A3>"",COUNTIF($B$2:$B$1000,"*"&A3&"*")>0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]house[/td][td]bla auto blub[/td][td]

TRUE​

[/td][td] =AND(A4>"",COUNTIF($B$2:$B$1000,"*"&A4&"*")>0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td]just something[/td][td]

FALSE​

[/td][td] =AND(A5>"",COUNTIF($B$2:$B$1000,"*"&A5&"*")>0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]