Find if a text cell contains a string of characters from an array in a different workbook

[kpieper876]

I've scoured the internet for this answer and have not found a formula that works.

I have a list of domain names/URLs in column A of workbook 1:
jhjh.com
hajfh.co.nz
123.123.12.12

I have a list of domain name extensions in workbook #2:
.com
.co.nz
.net

I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.

It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.

Thanks in advance.

 

[Denis S]

It Works!
but i forgot one thing, Sheet1 Column A, can be also filled like:

[TABLE="class: cms_table, width: 337"]
<tbody>[TR]
[TD]Igorwanna eat
[/TD]
[/TR]
[TR]
[TD]I know Charliewanna Eat
[/TD]
[/TR]
[TR]
[TD]Max killed Johnny[/TD]
[/TR]
[TR]
[TD]Johnny and Max-are friends
[/TD]
[/TR]
[TR]
[TD]U know_Charlie_andJoe drunk a lot of alcohol yesterday
[/TD]
[/TR]
</tbody>[/TABLE]

so i need use wildcard somehow....

if i put

=LOOKUP(9.99999999999999E+307, SEARCH("&"&Sheet2!$A$2:$A$8&"&"," "&A2&" "),Sheet2!$A$2:$A$8)

but it returns zero ((</pre>

 

[Aladin Akyurek]

It Works!
but i forgot one thing, Sheet1 Column A, can be also filled like:


so i need use wildcard somehow....

if i put

but it returns zero ((

Try without space delimiting...

=LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$8,A2),Sheet2!$A$2:$A$8)