|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Returning Multiple Lookup values as a comma separated list from another Comma Separated List.
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 |
#2
|
||||
|
||||
Test and check the attached.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
LOOKUP Not returning values | justa_guy_32405 | Excel Programming | 0 | 08-06-2022 12:37 PM |
Create bookmark from comma separated text | jeffreybrown | Word VBA | 8 | 08-18-2019 02:05 PM |
Do not calculate when multiple separated cells are all blank | JBader | Excel | 3 | 11-13-2018 10:20 AM |
Merge Fields are not separated | mendelsohnf | Mail Merge | 5 | 11-07-2016 12:35 PM |
Bullet List Being Separated On Two Pages | spearo | Word | 1 | 09-01-2014 05:50 AM |