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

JCluff

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?

Paul Newcome

You would need a helper column on the source sheet that joins all of the names together into a single cell (on each row). From there you can run an INDEX/MACTH on the second sheet to pull over the joined column.