+ Reply to Thread
Results 1 to 2 of 2

HDI - Return multiple keywords in a text column based on a set of available keywords

  1. #1
    Registered User
    Join Date
    09-25-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    1

    HDI - Return multiple keywords in a text column based on a set of available keywords

    I've checked around the forum and haven't been able to find a solution that matches what I need (at least using the examples given thus far). I have a text field where users have historically been able to type whatever they want. In converting to a new system, we have a list of available keywords which will be used in a drop down. For the conversion, we want to return each keyword contained in the text field, ideally with comma separation.

    I am attaching the following spreadsheet. The Injuries sheet contains examples of text entered. I have included a highlighted column using the formula below. I have then created a manual 3rd column which has my desired results. So far, with the formula below, I can only get it to return one keyword value based on the Keywords tab. It appears it is always pulling the last available one in that list if there are multiple.


    =IFERROR(LOOKUP(1E+100,SEARCH(Keywords!$A$2:$A$92,A290),Keywords!$A$2:$A$92),"")
    Last edited by zneiley; 09-25-2013 at 01:19 PM. Reason: Forgot attachment

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: HDI - Return multiple keywords in a text column based on a set of available keywords

    Hi,

    Concatenating multiple entries into a single cell without resource to VBA is not so feasible, particularly if the number of returns is large.

    If you are willing to accept separate cells for each return, then this array formula (important that you know how to enter this type of formula in Excel) in D2 and copied across and down will give you your desired results:

    =IFERROR(INDEX(Keywords!$A$2:$A$10,SMALL(IF(ISNUMBER(SEARCH(Keywords!$A$2:$A$10,Injuries!$A2)),ROW(Keywords!$A$2:$A$10)-MIN(ROW(Keywords!$A$2:$A$10))+1),COLUMNS($A:A))),"")

    If not, then I trust someone will shortly offer you the required VBA solution.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search for multiple keywords within a single text cell
    By ndube50 in forum Excel General
    Replies: 22
    Last Post: 06-30-2020, 07:23 AM
  2. [SOLVED] Return Category based on Keywords in a cell from array
    By nazsha07 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2012, 12:25 AM
  3. [SOLVED] IF cant find selection of multiple keywords return "text"
    By Eagle75au in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2012, 02:52 AM
  4. Replies: 2
    Last Post: 11-24-2010, 05:44 PM
  5. Replies: 1
    Last Post: 01-15-2010, 05:29 AM

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