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.
<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>
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 | ||||
THINGS | ITEM | INLIST | ||
BAT | BAT & BALL | TRUE | ||
BALL | THE BAT'S WINGS ARE BLACK | TRUE | ||
GLOVE | DOLL, TEAPOT | TRUE | ||
DOLL | CAR, MOTORCYLCE, TRUCK, PLANE | TRUE | ||
TRUCK | PLANE, CAR | FALSE | ||
GLOVE | TRUE | |||
DOVE | FALSE | |||
LOVE | 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>
</tbody>