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.

Paul Newcome

Are you able to provide a screenshot of manually entered data that reflects the desired outcome based on your reference sheet screenshot above?

JCluff

Paul Newcome

Aug 12, 2022

Are you able to provide a screenshot of manually entered data that reflects the desired outcome based on your reference sheet screenshot above?

I want the formula to be smart enough to do a join(collect() for me based on what project name is listed on sheet 2.

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.

JCluff

Paul Newcome

Aug 12, 2022

Are you able to provide a screenshot of manually entered data that reflects the desired outcome based on your reference sheet screenshot above?

I figured as much. I was just hoping to avoid another helper column. Thank you!

Paul Newcome

Happy to help. 👍️