+ Reply to Thread
Results 1 to 4 of 4

Searching whether a cell contains one or more keywords from a dynamic list

  1. #1
    Registered User
    Join Date
    11-20-2012
    Location
    USA
    MS-Off Ver
    Excel 20010
    Posts
    2

    Searching whether a cell contains one or more keywords from a dynamic list

    Hello
    I'm new to this forum.
    I've used Excel only for vanilla projects and I'm trying to do something more complicated.
    I'd like to create a dynamic list of keywords - something I can change on occasion, and apply this list to column with text entries.
    If any keyword from the list is contained within a cell's text, I'd like to copy another cell to the next column (in order to sum the new column).
    I'm attaching a mock-up spreadsheet for illustration purposes.
    Any ideas?
    Thanks for your help.
    Georgi
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,757

    Re: Searching whether a cell contains one or more keywords from a dynamic list

    Put your keywords in individual cells, for example D2, E2 and F2 might contain keywords, keyword1 and keyword2 respectively, then you can use this formula in D4:

    =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(D$2:F$2,B4))))>0,C4,"")

    and copy down as far as you need to.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-20-2012
    Location
    USA
    MS-Off Ver
    Excel 20010
    Posts
    2

    Re: Searching whether a cell contains one or more keywords from a dynamic list

    Quote Originally Posted by Pete_UK View Post
    Put your keywords in individual cells, for example D2, E2 and F2 might contain keywords, keyword1 and keyword2 respectively, then you can use this formula in D4:

    =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(D$2:F$2,B4))))>0,C4,"")

    and copy down as far as you need to.

    Hope this helps.

    Pete
    Thanks Pete. This is very helpful.
    I was looking to have a dynamic list of keywords, but I can replace that with a big-size fixed-length array of keywords and stuff the array with "dummies", leaving space to add more keywords as needed.
    Thanks again!
    Georgi

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,757

    Re: Searching whether a cell contains one or more keywords from a dynamic list

    Well you could have a dynamic named range (eg called "keywords") and then the formula would be:

    =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(keywords,B4))))>0,C4,"")

    Then you could put your keywords anywhere (eg in column K, starting with K1) and then define the named range with this in the refers to box:

    =INDIRECT("Sheet1!K1:K"&COUNTA(Sheet1!$K:$K))

    and this will automatically adjust to the number of entries you have in column K.

    Hope this helps.

    Pete

+ 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