+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1

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

    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.


  2. #2
    Harlan Grove
    Guest

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

    [email protected] 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"

    ....

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


  3. #3
    Duke Carey
    Guest

    RE: search a text cell CONTAINING matching text in a dynamic range (no

    Maybe:

    =IF(SUMPRODUCT(--(A2=ExcludeList))>0,"Exclude","")

    "[email protected]" wrote:

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


  4. #4

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

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


  5. #5
    Registered User
    Join Date
    05-07-2011
    Location
    dubai
    MS-Off Ver
    Excel 2010
    Posts
    4

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

    hi i have similar problem, i guess you guys can help.
    i have two columns A and B, with a alphanumeric text, i want a code to extract value of column C, if in a row column A (which is long text contain Alphanumeric) contains word "ABC3" and B contain word "CDE6".

    any help will be highly appreciated.
    thanks
    Last edited by khalidnisar; 04-02-2012 at 07:40 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1