Text Contains one of many Things

[TheRogue]

I have a dynamic list of things. Depending upon what else happens in the worksheet, this list could contain 1-10 entries.
I have given it the Named Range of "THINGS" (THINGS=Table1[THINGS]).

I have a 2nd table. One column of this table has text strings (Table2[ITEM]). In this table, I need a column (Table2[INLIST]) which will check to see if any of the words from THINGS appears in Table2[ITEM].

I am using the formula: {=SUMPRODUCT(--ISNUMBER(SEARCH(THINGS,[@ITEM])))>0}
but it is returning a value of TRUE on everything (I believe b/c THINGS contains blanks).

I have no control of what is in THINGS or how many entries it contains. I just need a TRUE/FALSE in Table2[INLIST], if Table2[ITEM] contains any of the words currently in THINGS.

OF UNIQUE
THINGS
BAT			TRUE
BALL			TRUE
GLOVE			TRUE
DOLL			TRUE
TRUCK			FALSE
			TRUE
			FALSE
			FALSE

<colgroup><col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:7972;width:164pt" width="218"> <col style="mso-width-source:userset;mso-width-alt:2669;width:55pt" width="73"> </colgroup><tbody>
[TD="width: 143, colspan: 2"]DYNAMIC LIST[/TD]
[TD="width: 64"][/TD]
[TD="width: 218"][/TD]
[TD="width: 73"][/TD]

[TD="class: xl64"]ITEM[/TD]
[TD="class: xl63, width: 73"]INLIST[/TD]

[TD="class: xl64"]BAT & BALL[/TD]

[TD="class: xl64"]THE BAT'S WINGS ARE BLACK[/TD]

[TD="class: xl64"]DOLL, TEAPOT[/TD]

[TD="class: xl64"]CAR, MOTORCYLCE, TRUCK, PLANE[/TD]

[TD="class: xl64"]PLANE, CAR[/TD]

[TD="class: xl64"]GLOVE[/TD]

[TD="class: xl64"]DOVE[/TD]

[TD="class: xl64"]LOVE[/TD]

[TD="class: xl64"][/TD]

[TD="class: xl64"][/TD]

[TD="class: xl64"][/TD]

[TD="class: xl64"][/TD]

</tbody>

 

[Aryatect]

Hi, your formula works for me, there is another option too:

	A	B	C	D	E	F
BAT	ITEM	INLIST	INLIST 2
BALL	BAT & BALL
GLOVE	THE BAT'S WINGS ARE BLACK
DOLL	DOLL, TEAPOT
TRUCK	CAR, MOTORCYLCE, TRUCK, PLANE
PLANE, CAR
GLOVE
DOVE
LOVE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$5,D2)))>0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=ISNUMBER(AGGREGATE(15,6,SEARCH($A$1:$A$5,D2),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Edit: Sorry just noticed the gap, will try to get a solution

 

[Aryatect]

This should work:

Book1
	A	B	C	D	E
1	BAT			ITEM	INLIST
2	BALL			BAT & BALL	TRUE
3	GLOVE			THE BAT'S WINGS ARE BLACK	TRUE
4	DOLL			DOLL, TEAPOT	TRUE
5				CAR, MOTORCYLCE, TRUCK, PLANE	TRUE
6	TRUCK			PLANE, CAR	FALSE
7				GLOVE	TRUE
8				DOVE	FALSE
9				LOVE	FALSE
Sheet2

Cell Formulas
Range	Formula
E2	{=SUMPRODUCT(IFERROR(SEARCH($A$1:$A$6,D2)*($A$1:$A$6<>""),0))>0}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jtakw]

Hi,

Try this, normally entered:

Book1
	A	B	C	D
1	THINGS		ITEM	INLIST
2	BAT		BAT & BALL	TRUE
3	BALL		THE BAT'S WINGS ARE BLACK	TRUE
4	GLOVE		DOLL, TEAPOT	TRUE
5	DOLL		CAR, MOTORCYLCE, TRUCK, PLANE	TRUE
6			PLANE, CAR	FALSE
7	TRUCK		GLOVE	TRUE
8
9			DOVE	FALSE
10			LOVE	FALSE
Sheet708

Cell Formulas
Range	Formula
D2	=IF(C2="","",ISNUMBER(LOOKUP(2,1/(SEARCH(A$2:A$7,C2)*(A$2:A$7<>"")))))

Change/adjust cell references/range/Named Range as needed.

Please note, you wanted TRUE for C3 for the word BAT'S in C3 because the word BAT is in A2, this poses a question...What if TRUCKER is in C11, should that be a TRUE or FALSE since the word TRUCK is in A7?

 

[Peter_SSs]

this poses a question...What if TRUCKER is in C11, should that be a TRUE or FALSE since the word TRUCK is in A7?

Extending on that BAT'S-related question, what if 'RED BALLOON' was in column C? Even though they are unrelated, BALLOON does contain BALL.

 

[TheRogue]

In the case of "Trucker" or "Balloon" it should return an answer of TRUE (Why I included the example of Bat's Wings. As long as it contains the series, "B-A-T" (in order, without other letters inbetween), it should be TRUE. "BAnTer" would return FALSE)

 

[jtakw]

Then you can just use my formula in Post # 4 as-is.