Search to find cells that contain at least two instances of keywords from a predefined set

**akholodenko** · 08-12-2012, 09:43 PM

Hi All,
I'm not sure how well I was able to describe what I was looking for in the title. If anybody can lend a hand I would greatly appreciate it.
Here's what I'm looking for.

I have an column that contains cells that all contain substrings seperated by commas (i.e. one cell may say only have "dog" another may say "dog, cat" another may say "dog, cat, monkey9, tiger" and another "monkey7, tiger 4" etc.)

I have a set of approximately 10 keywords (i.e. monkey7, monkey9, tiger 4.......). I am trying to search for the cells in said column that contain AT LEAST two of the keywords from the list.
Can anyone help me out here?
Hope I was clear in my explanation.
Thanks.

**Max, Singapore** · 08-12-2012, 10:12 PM

Assume E2:E11 will contain the 10 keywords
Assume your source data runs in B2 down
Put this in C2: =IF(SUMPRODUCT(--(ISNUMBER(SEARCH($E$2:$E$11,B2))))>=2,"x","")
Copy down, then filter col C for "x" to get the desired col B results
Note that E2:E11 should be fully populated, ie it should NOT contain any blank cells

Search to find cells that contain at least two instances of keywords from a predefined set

LinkBack

Thread Tools

Rate This Thread

Display

Search to find cells that contain at least two instances of keywords from a predefined set

Re: Search to find cells that contain at least two instances of keywords from a predefined

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions