Lookup text in cell, find corresponding results & present these results in a single cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2174709%22%20slang%3D%22en-US%22%3ELookup%20text%20in%20cell%2C%20find%20corresponding%20results%20%26amp%3B%20present%20these%20results%20in%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2174709%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I%20have%20a%20workbook%20with%20two%20sheets.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOne%20sheet%20(QUESTIONS)%20contains%20a%20table%20of%20questions.%20Each%20question%20has%20a%20number%20of%20'Recommendations'%20against%20it%2C%20presented%20as%20a%20list%20in%20column%20F%2C%20separated%20by%20semi-colons.%20The%20same%20recommendation%20may%20be%20made%20for%20multiple%20questions%20and%20any%20combination%20of%20recommendations%20is%20possible.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20sheet%20(OWNERS)%20contains%20a%20list%20of%20the%20unique%20recommendations%20and%20the%20corresponding%20'Owners'.%20The%20same%20owner%20may%20be%20listed%20against%20several%20recommendations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20is%20to%20lookup%20the%20recommendations%20from%20column%20F%20of%20the%20QUESTIONS%20sheet%20and%20present%20a%20list%20of%20the%20owners%20for%20those%20recommendations%20in%20column%20G%2C%20separated%20by%20commas.%20So%20if%20a%20recommendation%20is%20added%20to%2010%20rows%20in%20the%20QUESTIONS%20sheet%2C%20the%20relevant%20owner%20will%20be%20added%20in%20column%20G%20for%20each%20of%20those%20questions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20thinking%20so%20far...%20we%20need%20to%20look%20up%20the%20values%20in%20column%20A%20of%20the%20recommendations%20sheet%20to%20see%20which%20of%20these%20strings%20of%20text%20exist%20in%20column%20F%20of%20the%20Questions%20sheet.%20For%20all%20the%20ones%20where%20there%20is%20a%20match%2C%20we%20then%20need%20to%20take%20the%20Owners%20name%20from%20column%20B%20of%20the%20Owners%20sheet%20and%20concatenate%20these%20and%20present%20them%20back%20in%20column%20G%20of%20the%20Questions%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20exploring%20using%20arrays%2C%20lookups%2C%20wildcard%20search%2C%20concatenated%20results%2C%20etc...%20but%20can't%20find%20a%20way%20to%20make%20these%20all%20work%20together.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20Excel%202016%20with%20O365%20subscriptions%20-%20test%20file%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2174709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2174888%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20text%20in%20cell%2C%20find%20corresponding%20results%20%26amp%3B%20present%20these%20results%20in%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2174888%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F982316%22%20target%3D%22_blank%22%3E%40fjp95%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTEXTJOIN(CHAR(10)%2CTRUE%2CIFERROR(INDEX(OWNERS!%24B%242%3A%24B%2423%2CMATCH(SUBSTITUTE(FILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(SUBSTITUTE(F4%2C%22%26amp%3B%22%2C%22%23%22)%2C%22%3B%22%26amp%3BCHAR(10)%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)%2C%22%23%22%2C%22%26amp%3B%22)%2COWNERS!%24A%242%3A%24A%2423%2C0))%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi everyone, I have a workbook with two sheets. 

 

One sheet (QUESTIONS) contains a table of questions. Each question has a number of 'Recommendations' against it, presented as a list in column F, separated by semi-colons. The same recommendation may be made for multiple questions and any combination of recommendations is possible. 

 

The second sheet (OWNERS) contains a list of the unique recommendations and the corresponding 'Owners'. The same owner may be listed against several recommendations.

 

What I would like to do is to lookup the recommendations from column F of the QUESTIONS sheet and present a list of the owners for those recommendations in column G, separated by commas. So if a recommendation is added to 10 rows in the QUESTIONS sheet, the relevant owner will be added in column G for each of those questions.

 

My thinking so far... we need to look up the values in column A of the recommendations sheet to see which of these strings of text exist in column F of the Questions sheet. For all the ones where there is a match, we then need to take the Owners name from column B of the Owners sheet and concatenate these and present them back in column G of the Questions sheet.

 

I have been exploring using arrays, lookups, wildcard search, concatenated results, etc... but can't find a way to make these all work together. 

 

Using Excel 2016 with O365 subscriptions - test file attached

 

Any help appreciated.

 

Thanks!

3 Replies

@fjp95 

=TEXTJOIN(CHAR(10),TRUE,IFERROR(INDEX(OWNERS!$B$2:$B$23,MATCH(SUBSTITUTE(FILTERXML("<y><z>"&SUBSTITUTE(SUBSTITUTE(F4,"&","#"),";"&CHAR(10),"</z><z>")&"</z></y>","//z"),"#","&"),OWNERS!$A$2:$A$23,0)),""))

@Detlef Lewin Hi Detlef - thanks so much for your response. I am not getting any errors with this formula but am also not getting any results. Have you managed to get this working on your end / are you able to share the sheet back over?

 

Thanks

@fjp95 

That suggests that every result is an error which gets caught by IFERROR() and changed to "".