+ Reply to Thread
Results 1 to 10 of 10

Match Cell Substrings to Column and Return Adjacent Cell

  1. #1
    Registered User
    Join Date
    08-05-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2010
    Posts
    4

    Match Cell Substrings to Column and Return Adjacent Cell

    I am trying to check if each cell in a column (Column D) contains a keyword, found in Column F. There is an associated name (Column B) next to each entry in Column D. I want to search for each keyword in Column D and return the Column B name if the keyword is contained in D.

    From Google searching, I have this formula so far: =IFERROR(LOOKUP(1E+100,SEARCH(F1,D3:D9),B3:B9),""). However, it will only return the name associated with the last match in D. I would like to output a list of names if multiple cells in D have a match to that specific keyword.

    I am not worried about looping through Column F for each keyword -- I want to paste the formula next to each keyword in F so that it is obvious which names are associated with which keywords.

    Any help would be appreciated!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,924

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    Hi there... and welcome to the Excel Forum. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    The paperclip icon
    Glenn



  3. #3
    Registered User
    Join Date
    08-05-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2010
    Posts
    4

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    I have attached a sample worksheet -- the columns have slightly changed from my original description. The list of keywords is in Column D, and the desired output is in Column E.

    Sample Excel.xlsx

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,924

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    Here's a UDF borrowed from Chip Pearson (enable macros on opening)

    When used in combination with this array formula:
    =StringConcat(", ",IF(ISNUMBER(FIND(B3,$G$1:$G$7)),$F$1:$F$7,""),"")

    it delivers what you need.
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,924

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    I meant to add...Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...

  6. #6
    Registered User
    Join Date
    08-05-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2010
    Posts
    4

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    Thanks for the file, Glenn. I am a little confused -- I tried to add another row and use the formula, but it isn't updating column G with the new information. I was writing in "Colors, Chairs" and then copying and pasting the formula in C7 (updating B7). The cell keeps going blank, however. Am I using this correctly?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,924

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    Il get back to you in a few hours. I'm in the cinema right now.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,924

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    Oh $%&*£... I think I may have put the formula in the wrong place!!!! Can you confirm WHICH column is the one that you want the result in. Currently, I have it delivering a result in C. If it should be G, then put this in G1, array enter it and drag down as needed.

    =StringConcat(", ",IF(ISNUMBER(FIND(F1,$C$3:$C$10)),$B$3:$B$10,""),"")
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-05-2015
    Location
    Philadelphia, PA
    MS-Off Ver
    2010
    Posts
    4

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    The new file works now! Column C was the input and G was the output. Thanks. So I can just copy and paste my actual data into that file, correct?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,924

    Re: Match Cell Substrings to Column and Return Adjacent Cell

    No, not quite.

    Open "my" sheet, on the sheet Tab, right click the mouse and select View code. Double click on Module 1. Select the code and copy it. On your own sheet, same thing: right click, select code, INSERT/MODULE and paste the code in. Save as Macro enabled, and then that should be you....

+ 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. [SOLVED] If a cell in a column equals today's date return the value of another cell adjacent to it
    By DeanExcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-24-2021, 01:38 AM
  2. [SOLVED] Function to match text and return number in adjacent cell?
    By danoswalt in forum Excel General
    Replies: 6
    Last Post: 12-16-2014, 01:47 PM
  3. Search for a text string and return adjacent cell value for each match
    By ral8088 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2013, 09:55 AM
  4. [SOLVED] Match multiple substrings in a cell against a lookup table and return concatenated values
    By stevewc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2013, 10:50 AM
  5. Match cell value in another workbook & return adjacent cell value
    By WT2008 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-15-2013, 05:00 PM
  6. Find and Match Text in Column Return Adjacent Cell
    By biancam in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2012, 02:45 PM
  7. Index/Match and return adjacent cell or workaround
    By alltimetop100 in forum Excel General
    Replies: 2
    Last Post: 03-02-2011, 08:24 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