Return all rows that match any names in a master list

jayayyann

Hi! I have been trying to return set of rows from a data set (separate sheet) with one of the columns (Action Owners) being the criteria range. However, the criteria is not just one value, but I want it such that if the name in Action Owner matches any name my "master list" of names (separate sheet), it will return those rows.

Will make it easier to add/change names in future, compared to using the filter functionality of reports.

Please help!

I have tried to use INDEX and COLLECTS but I know something is missing because it does not iterate through my list of names:

=INDEX(COLLECT({Data set Range}, {Action Owners Range}, CONTAINS({Team Members}, @cell)), 1)

Paul Newcome

Try a JOIN function instead of an INDEX function.

jayayyann

Hi @Paul Newcome thanks for the suggestion.

Even if I change to JOIN instead of INDEX, I have a problem that I am unable to iterate through my master list (in bold). I know that CONTAINS doesn't quite do the job but I can think of any other way to go about it.

=INDEX(COLLECT({Data set Range}, {Action Owners Range}, CONTAINS({Team Members}, @cell)), 1)

Paul Newcome

Are you able to provide some screenshots for reference?