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.

 

[Robert Mika]

Try this:
The domain list is on Workbook 1 Sheet 1 column A
The list of name extension in Workbook 2 Sheet 1 column B

In cell B1 Sheet1 Workbook 1:
=IF(ISNUMBER(MATCH("*"&[Book2.xlsx]Sheet1!B1&"*",$A$1:$A$3,0)),"True","False")copy down

 

[kpieper876]

Doesn't seem to be working. Let me try to explain a different way.

I need to see if the workbook 1 cell A1 contains any of the character strings in workbook 2 column A. It looks like what you wrote here does not reference the array in workbook 2 but rather an array in workbook 1 (I could be misunderstanding the match function).

=IF(ISNUMBER(MATCH("*"&[Book2.xlsx]Sheet1!B1&"*",$A$1:$A$3,0)),"True","False")

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">jhjh.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">hajfh.co.nz</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">123.123.12.12</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">jhjh.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">123.123.12.12</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">************</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISERR(<font color="Green">FIND(<font color="Purple">[wokbook02.xlsx]Sheet1!A$1:A$3,A1</font>)</font>)</font>)<>ROWS(<font color="Red">[wokbook02.xlsx]Sheet1!A$1:A$3</font>),"True","False"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Robert Mika]

My formula is taking as lookup value the domain name(let say .com ) in workbook 2 and check if there is a match in column A workbook 1.

The first two example will come with value True the last with False.

Not sure about this part "note that I need to look at the string, not the entire cell in workbook one"

 

[Rick Rothstein]

Maybe this...

=IF(SUMPRODUCT(--ISNUMBER((SEARCH("*"&'[workbook02.xlsx]Sheet1'!$A$1:$A$3,A1)))),TRUE,FALSE)

Assumes Column A is being used for each list in its own workbook. Obviously change wrokbook02.xlsx to your actual workbook name. Oh, Sheet1 is assumed to have the list in workbook02.

 

[Aladin Akyurek]

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.

WB1, Sheet1

<TABLE style="WIDTH: 131pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=174><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3925" width=110><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=110> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>jhjh.com</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=center>TRUE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>hajfh.co.nz</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=center>TRUE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>123.123.12.12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2826116 class=xl65 align=center>FALSE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

WB2, Sheet1

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2826116 class=xl66 height=19>.com</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>.co.nz</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19>.net</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2826116 class=xl65 height=19> </TD></TR></TBODY></TABLE>

In WB1, B2 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH([WB2.xlsx]Sheet1!$A$2:$A$4,A2)))

 

[kpieper876]

Beautiful! Thanks Rick, those final tweaks did the trick.

 

[markmzz]

Another way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">jhjh.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">hajfh.co.nz</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">123.123.12.12</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">jhjh.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">123.123.12.12</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">123.com.123</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">************</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">IFERROR(<font color="Red">LOOKUP(<font color="Green">TRUE,FIND(<font color="Purple">[wokbook02.xlsx]Sheet1!A$1:A$3,A1</font>)+LEN(<font color="Purple">[wokbook02.xlsx]Sheet1!A$1:A$3</font>)-1=LEN(<font color="Purple">A1</font>)</font>),FALSE</font>),"True","False"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[markmzz]

Another way (a little small formula):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">jhjh.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">hajfh.co.nz</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">123.123.12.12</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">jhjh.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">123.123.12.12</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">123.com.123</td><td style="text-align: center;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">************</td><td style="text-align: center;;">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">SUMPRODUCT(<font color="Red">--ISNUMBER(<font color="Green">FIND(<font color="Purple">[wokbook02.xlsx]Sheet1!A$1:A$3,RIGHT(<font color="Teal">A1,LEN(<font color="#FF00FF">[wokbook02.xlsx]Sheet1!A$1:A$3</font>)</font>)</font>)</font>)</font>),"True","False"</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz