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

[fjp95]

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!

[#2]

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

[#3]

@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

[#4]

@fjp95 

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