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

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.



3 Replies



@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?




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