Creating a membership list

New Contributor

Hi all


I'm trying to create a list of people who report to a number of Departments


In the example above, this should output the following list


It feels like there should be a quick way to do this without having to resort to VBA?




5 Replies
Assuming you have Excel 365 you can easily use the FILTER() function: =FILTER(table[NAME], table[Dept 1]="x", "none")

Thanks @mtarler


I've defined the data (B2:G7) as a table called Department_members and used the function below. However, the data it's returning (circled in the screenshot) sin't what I'm expecting. Any further advice?


=FILTER(Department_members, Department_members[Dept 1]="x", "none")




best response confirmed by tlangfordindk (New Contributor)

Hi @tlangfordindk 


(EDIT: Picture shows wrong results but can't be replaced - sorry)


in I3 then copy right until M3:


=FILTER(Department_members[[Name]:[Name]], Department_members[Dept 1]="x", "none")


Amazing, that's exactly what I need!

Thanks for both of your help

@tlangfordindk. Glad we could help - ALL credit @mtarler & Thanks for providing feedback