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

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

Post by msgoldcu » Thu, 21 Jul 2005 01:16:03


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.
 
 
 

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

Post by Harlan Gro » Thu, 21 Jul 2005 01:52:09


XXXX@XXXXX.COM wrote...
...
...

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].

 
 
 

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

Post by msgoldcu » Thu, 21 Jul 2005 03:20:03

{=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*