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:

Igorwanna eat

I know Charliewanna Eat

Max killed Johnny

Johnny and Max-are friends

U know_Charlie_andJoe drunk a lot of alcohol yesterday

<tbody>
</tbody>

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)