search a text cell CONTAINING matching text in a dynamic range (not exact match)

  • Thread starter Thread starter msgoldcup
  • Start date Start date
M

msgoldcup

Hi,

I use the following, currently, to mark a row for exclusion

=IF(AND(ISERROR(SEARCH("GENERIC",A2,1)),ISERROR(SEARCH("PSOFT",A2,1)),ISERROR(SEARCH("dnd",A2,1)),ISERROR(SEARCH("temp",A2,1)),ISERROR(SEARCH("train",A2,1)),ISERROR(SEARCH("do
not delete",A2,1))),"","EXCLUDE")

I'm looking for a way to use an exclusion keyword list on a separate
sheet in a dynamic range
so users may add to or delete exclusion keywords
rather than have the 'hardcoded' exclusion keywords in the formula
above

And I'm hoping to have the resulting value be the keyword found rather
than "EXCLUDE"

I've fiddled around with index/match (something I haven't quite
mastered)
and I have not found a way to make it work correctly(maybe because I am
searching for key words contained in cells that have sentences rather
han exact matches?)

This isn't working
=INDEX(ExcludeList,MATCH(ExcludeList,A2,1),1)
(the Range is named ExcludeList)

I'm wondering if there is a way to use SEARCH somehow but that doesn't
seem to work with a named range?

Any help is appreciated.
 
(e-mail address removed) wrote...
....
I use the following, currently, to mark a row for exclusion

=IF(AND(ISERROR(SEARCH("GENERIC",A2,1)),ISERROR(SEARCH("PSOFT",
A2,1)),ISERROR(SEARCH("dnd",A2,1)),ISERROR(SEARCH("temp",A2,1)),
ISERROR(SEARCH("train",A2,1)),ISERROR(SEARCH("do not delete",
A2,1))),"","EXCLUDE")

I'm looking for a way to use an exclusion keyword list on a separate
sheet in a dynamic range
so users may add to or delete exclusion keywords
rather than have the 'hardcoded' exclusion keywords in the formula
above

And I'm hoping to have the resulting value be the keyword found rather
than "EXCLUDE"
Click to expand...
....

So you want to search cell A2 for instances of strings from a list in
another worksheet? That list would be user-entered? That list would be
variable size? And if any of the strings in the list were found in
cell A2, you want the formula to return that string?

If so, then if users enter search strings in Other!B2:B65536, name
that range something like LST and use array formulas like

=TRIM(INDEX(LST,MATCH(1,COUNTIF(A2,"*"&TRIM(LST)&"*"),0)))

Note: array formulas are entered by typing the formula, then holding
down [Ctrl] and [Shift] keys before pressing [Enter].
 
{=TRIM(INDEX(ExcludeList,MATCH(1,COUNTIF(A2,"*"&TRIM(ExcludeList)&"*"),0)))}

That worked precisely as needed.

Searched in a cell of comments for keywords in a user modifiable list
and reported the matching keyword!

Thank you! Thank you! Thank you!
*happy dance*
 
Back
Top