Match Cell Substrings to Column and Return Adjacent Cell

**ashb444** · 08-05-2015, 10:52 AM

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!

**Glenn Kennedy** · 08-05-2015, 11:03 AM

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

**ashb444** · 08-05-2015, 11:16 AM

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

**Glenn Kennedy** · 08-05-2015, 11:34 AM

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.

**Glenn Kennedy** · 08-05-2015, 11:36 AM

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

**ashb444** · 08-05-2015, 12:56 PM

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?

**Glenn Kennedy** · 08-05-2015, 01:26 PM

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

**Glenn Kennedy** · 08-05-2015, 04:11 PM

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,""),"")

**ashb444** · 08-06-2015, 11:10 AM

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?

**Glenn Kennedy** · 08-06-2015, 11:24 AM

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

Match Cell Substrings to Column and Return Adjacent Cell

LinkBack

Thread Tools

Rate This Thread

Display

Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Re: Match Cell Substrings to Column and Return Adjacent Cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If a cell in a column equals today's date return the value of another cell adjacent to it

[SOLVED] Function to match text and return number in adjacent cell?

Search for a text string and return adjacent cell value for each match

[SOLVED] Match multiple substrings in a cell against a lookup table and return concatenated values

Match cell value in another workbook & return adjacent cell value

Find and Match Text in Column Return Adjacent Cell

Index/Match and return adjacent cell or workaround

Bookmarks

Bookmarks

Posting Permissions