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.

 

[tjyoung]

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.

Rick (or anyone), can you please explain to me how your combination works? I had a similar issue and yours worked for me as well. However, I get confused how the SUMPRODUCT formula helps. I now realize that this is the crucial part to the formula, but I do not understand how it is "sumproduct-ing" and what exactly it is "sumproduct-ing". It seems that what is inside the SUMPRODUCT() is either a zero or a one, so I am very confused. Thanks for your help!

 

[Aladin Akyurek]

Rick (or anyone), can you please explain to me how your combination works? I had a similar issue and yours worked for me as well. However, I get confused how the SUMPRODUCT formula helps. I now realize that this is the crucial part to the formula, but I do not understand how it is "sumproduct-ing" and what exactly it is "sumproduct-ing". It seems that what is inside the SUMPRODUCT() is either a zero or a one, so I am very confused. Thanks for your help!

SEARCH creates 3 evaluations corresponding to the items from [workbook02.xlsx]Sheet1'!$A$1:$A$3 with respect to the string in A1: each evaluation is either a position value or a #VALUE! error. For example, something like:

{1;#VALUE!;#VALUE!}
{#VALUE!;1;#VALUE!}
{#VALUE!;#VALUE!;#VALUE!}

Applyinf ISNUMBER to the foregoing, we get...

{TRUE;FALSE;FALSE}
{FALSE;TRUE;FALSE}
{FALSE;FALSE;FALSE}

Applying -- to the ISNUMBER results, we get:

{1;0;0}
{0;1;0}
{0;0;0}

Applying SUMPRODUCT to the foregoing gives:

1
1
0

Applying IF to the foregoing, i.e.,

IF(1,TRUE,FALSE) --> TRUE
IF(1,TRUE,FALSE) --> TRUE
IF(0,TRUE,FALSE) --> FALSE

The same results can be obtained in a faster way also with:

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

 

[Rick Rothstein]

SEARCH creates 3 evaluations corresponding to the items from [workbook02.xlsx]Sheet1'!$A$1:$A$3 with respect to the string in A1: each evaluation is either a position value or a #VALUE! error. For example, something like:

{1;#VALUE!;#VALUE!}
{#VALUE!;1;#VALUE!}
{#VALUE!;#VALUE!;#VALUE!}

Applyinf ISNUMBER to the foregoing, we get...

{TRUE;FALSE;FALSE}
{FALSE;TRUE;FALSE}
{FALSE;FALSE;FALSE}

Applying -- to the ISNUMBER results, we get:

{1;0;0}
{0;1;0}
{0;0;0}

Applying SUMPRODUCT to the foregoing gives:

1
1
0

Applying IF to the foregoing, i.e.,

IF(1,TRUE,FALSE) --> TRUE
IF(1,TRUE,FALSE) --> TRUE
IF(0,TRUE,FALSE) --> FALSE

The same results can be obtained in a faster way also with:

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

Thanks for jumping in here and explaining the formula for me. I got a new computer (mostly up now) but before I could transfer all the data from my old computer, its water cooler pump broke and that part's failure prevented the old computer from starting up. I have now jury-rigged a solution to get the old computer running and am now in the process of transferring the data (it's going sloooowly) from one computer to the other; hence, now only has this kept me off-line, but I have lost touch with any threads I had participated in. I should be "back in business" full-time by next week I figure.

 

[Aladin Akyurek]

Thanks for jumping in here and explaining the formula for me. I got a new computer (mostly up now) but before I could transfer all the data from my old computer, its water cooler pump broke and that part's failure prevented the old computer from starting up. I have now jury-rigged a solution to get the old computer running and am now in the process of transferring the data (it's going sloooowly) from one computer to the other; hence, now only has this kept me off-line, but I have lost touch with any threads I had participated in. I should be "back in business" full-time by next week I figure.

Yeah, it's always a tough experience. Sterkte (i.e., Dutch).

 

[Denis S]

Hello guys, i have similar problem, but dont know why your solutions does not worked, could u help me pls ?

sheet 3
column A

[TABLE="width: 95"]
<tbody>[TR]
[TD]jhjh.com.mlm[/TD]
[/TR]
[TR]
[TD]hajfh.co.nz.ewr[/TD]
[/TR]
[TR]
[TD]123.123.12.12[/TD]
[/TR]
[TR]
[TD]mama.com[/TD]
[/TR]
[TR]
[TD]jhjh.com[/TD]
[/TR]
[TR]
[TD]evro.eu[/TD]
[/TR]
</tbody>[/TABLE]

sheet 4
column A

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"].com[/TD]
[/TR]
[TR]
[TD].co.nz[/TD]
[/TR]
[TR]
[TD].net[/TD]
[/TR]
[TR]
[TD].eu[/TD]
[/TR]
</tbody>[/TABLE]

and my formula in sheet 3 column B is :
=IF(SUMPRODUCT(--ISNUMBER((SEARCH("*"&Sheet4!$A$1:$A$4&"*",A1)))),Sheet4!$A$1:$A$4,FALSE)

this is i have in result in Sheet 3 Column B

[TABLE="width: 127"]
<tbody>[TR]
[TD].com[/TD]
[/TR]
[TR]
[TD].co.nz[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD].eu[/TD]
[/TR]
[TR]
[TD]#VALUE![/TD]
[/TR]
[TR]
[TD]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]

what i need actually:

in Sheet 1 Column a i will have a list of about 200 descriptions which i need check if there mentioned value from Sheet 2 Column A there can be about 2000 words...
And in Sheet 1 Column B i need put appropriate value from Sheet 2 Column A.


could u help me pls ?

 

[Aladin Akyurek]

Hello guys, i have similar problem, but dont know why your solutions does not worked, could u help me pls ?

sheet 3
column A

sheet 4
column A


and my formula in sheet 3 column B is :
=IF(SUMPRODUCT(--ISNUMBER((SEARCH("*"&Sheet4!$A$1:$A$4&"*",A1)))),Sheet4!$A$1:$A$4,FALSE)

this is i have in result in Sheet 3 Column B

what i need actually:

in Sheet 1 Column a i will have a list of about 200 descriptions which i need check if there mentioned value from Sheet 2 Column A there can be about 2000 words...
And in Sheet 1 Column B i need put appropriate value from Sheet 2 Column A.


could u help me pls ?

Which sheets? The set of Sheet3 and Sheet4 or Sheet1 and Sheet2?

Sheet3, B2, just enter and copy down:

Either...

=IFERROR(LOOKUP(9.99999999999999E+307,
   SEARCH(Sheet4!$A$2:$A$200,$B2),Sheet4!$A$2:$A$200),"")

Or...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",
    LOOKUP(9.99999999999999E+307,SEARCH(Sheet4!$A$2:$A$200,$B2),
   Sheet4!$A$2:$A$200)))

 

[Denis S]

both codes return zero

 

[Aladin Akyurek]

both codes return zero

Try to post a very small sample along with the desired results.

 

[Denis S]

ok, so

sheet 1 column A:

[TABLE="width: 337"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Igor wanna eat[/TD]
[/TR]
[TR]
[TD]I know Charlie wanna Eat[/TD]
[/TR]
[TR]
[TD]Max killed Johnny[/TD]
[/TR]
[TR]
[TD]Johnny and Max are friends[/TD]
[/TR]
[TR]
[TD]U know Charlie and Joe drunk a lot of alcohol yesterday[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 column A:

[TABLE="width: 127"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Igor[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[/TR]
[TR]
[TD]Johnny[/TD]
[/TR]
[TR]
[TD]Max[/TD]
[/TR]
[TR]
[TD]Ive[/TD]
[/TR]
[TR]
[TD]Marie[/TD]
[/TR]
[TR]
[TD]Const[/TD]
[/TR]
</tbody>[/TABLE]

And i wanna see in Sheet 1:

[TABLE="width: 464"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Igor wanna eat[/TD]
[TD]Igor[/TD]
[/TR]
[TR]
[TD]I know Charlie wanna Eat[/TD]
[TD]Charlie[/TD]
[/TR]
[TR]
[TD]Max killed Johnny[/TD]
[TD]Johnny[/TD]
[/TR]
[TR]
[TD]Bob and Max are friends[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]U know Charlie and Joe drunk a lot of alcohol yesterday[/TD]
[TD]Charlie[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

ok, so

sheet 1 column A:


Sheet 2 column A:


And i wanna see in Sheet 1:

Sheet1, B2, just enter and copy down:

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