Hello All,

I've run into a roadblock trying to look up comma-separated values within a cell to return corresponding values in the same comma-separated format with two qualifiers in place. Please review the attachment for clarification as it can get confusing. Any help will be appreciated!

Im drafting a template document with two sheets. The first sheet contains data and the second sheet contains the template. For the sake of time, I've entered placeholder values within the template sheet however most of them draw information from the data sheet.



In "Column M" I have a comma-separated List in each cell which specifies the "Key Stakeholders" working on an Item using their titles. As this is a template, my goal is to return values for "Owner Names" to Column N using the list in Column M in the same comma-delimited format. Some Titles are shared by multiple Owners and the only way to differentiate them is via Faculty, which can be selected using cells N3 and N4 on the template. The returned values must only be from either of these two faculties selected.

Previously I was able to make a lookup with these two qualifiers work for Column I for individual Owner Titles (Column J) with this formula:

=IFERROR(INDEX('Data Sheet'!$B$2:$B$1002,AGGREGATE(15,6,ROW('Data Sheet'!$B$2:$B$1002)/('Data Sheet'!$C$2:$C$1002=J20)/(('Data Sheet'!$E$2:$E$1002=$N$3)+('Data Sheet'!$E$2:$E$1002=$N$4)),1)-1),"")

However, looking up a comma-separated list to return values in c-s format eludes me.

When working properly, the returned values should look like Cell N20 and must match either of the two qualifiers in cells N3 and N4. Please view the attachment for reference.

The overall goal is to have the values in Column N change when the faculty selection in cells N3/N4 changes.

Any help would be highly appreciated. totally stumped.

Best,
Aarish

Attached Files

Template Example.xlsx (21.5 KB, 6 views)

Test and check the attached.

Attached Files

msofficeforums50469Template Example.xlsx (22.4 KB, 4 views)