I have a sheet with a list of names (A), followed by a cell (B) which contains one or more years separated by commas.
I want to generate a list on a separate sheet of all names from A when B contains the number in D:
"Data" sheet:
A B 1 John Doe 1998, 2005, 2014 2 Jane Doe 2012, 2014, 2017 3 George Spelvin 2005, 2012, 2017
"List" sheet:
A B 1 2005 2012 2 John Doe Jane Doe 3 George Spelvin George Spelvin
I received a suggestion of the following formula, in cell A2 of the list table:
IF(A$1="","",IFERROR(INDEX(Data!$A$1:$A$3,AGGREGATE(15,6,(ROW(Data!$B$1:$B$3)-ROW(Data!B$1)+1)/ISNUMBER(FIND(A$1,Data!$B$1:$B$3)),ROWS(A$2:A2))),""))
Unfortunately it did not work. I'm new at this and unable to diagnose what would need fixing. Much obliged for any suggestions.
Bookmarks