Forum Discussion
Lookup text in cell, find corresponding results & present these results in a single cell
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
=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
