Text Contains one of many Things

TheRogue

New Member
Joined
Aug 3, 2019
Messages
23
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.

DYNAMIC LIST
OF UNIQUE
THINGSITEMINLIST
BATBAT & BALLTRUE
BALLTHE BAT'S WINGS ARE BLACKTRUE
GLOVEDOLL, TEAPOTTRUE
DOLLCAR, MOTORCYLCE, TRUCK, PLANETRUE
TRUCKPLANE, CARFALSE
GLOVETRUE
DOVEFALSE
LOVEFALSE

<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>
</tbody>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi, your formula works for me, there is another option too:

ABCDEF
1BATITEMINLISTINLIST 2
2BALLBAT & BALLTRUETRUE
3GLOVETHE BAT'S WINGS ARE BLACKTRUETRUE
4DOLLDOLL, TEAPOTTRUETRUE
5TRUCKCAR, MOTORCYLCE, TRUCK, PLANETRUETRUE
6PLANE, CARFALSEFALSE
7GLOVETRUETRUE
8DOVEFALSEFALSE
9LOVEFALSEFALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
E2=SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$5,D2)))>0
F2=ISNUMBER(AGGREGATE(15,6,SEARCH($A$1:$A$5,D2),1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Edit: Sorry just noticed the gap, will try to get a solution
 
Last edited:
Upvote 0
This should work:


Book1
ABCDE
1BATITEMINLIST
2BALLBAT & BALLTRUE
3GLOVETHE BAT'S WINGS ARE BLACKTRUE
4DOLLDOLL, TEAPOTTRUE
5CAR, MOTORCYLCE, TRUCK, PLANETRUE
6TRUCKPLANE, CARFALSE
7GLOVETRUE
8DOVEFALSE
9LOVEFALSE
Sheet2
Cell Formulas
RangeFormula
E2{=SUMPRODUCT(IFERROR(SEARCH($A$1:$A$6,D2)*($A$1:$A$6<>""),0))>0}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Hi,

Try this, normally entered:


Book1
ABCD
1THINGSITEMINLIST
2BATBAT & BALLTRUE
3BALLTHE BAT'S WINGS ARE BLACKTRUE
4GLOVEDOLL, TEAPOTTRUE
5DOLLCAR, MOTORCYLCE, TRUCK, PLANETRUE
6PLANE, CARFALSE
7TRUCKGLOVETRUE
8
9DOVEFALSE
10LOVEFALSE
Sheet708
Cell Formulas
RangeFormula
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?
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
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)
 
Upvote 0
Then you can just use my formula in Post # 4 as-is.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top