Return all rows that match any names in a master list


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)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!