Index(Match()) within Join(Collect())


I'm using a document similar to the below example where I need to pull a list of group names by project. I know Join with delimiter of char(10) will provide the list in the format I want, but I want it to be smart enough to provide it based on Project Name. I'm using a second sheet to reference the below example sheet and the only common factor is the Project Name.

Below are the functions I've tried with no luck:

=JOIN(COLLECT({Name1-Name5}, {ProjectNameColumn}, [Project Name]@row), CHAR(10))

=JOIN(INDEX({Name1-Name5}, MATCH([Project Name]@row, {ProjectNameColumn}, 0)), CHAR(10))

Is it possible to do a nested formula like this, or do I need to create a helper column in sheet 1 that Joins the names and then Index/Match this column?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!