Add JOIN Function to Index/Match

Sherry Fox

I am back, again! LOL Okay, My formula had worked initially, but that was when I only had one record. But since then I added added additional records, each with their own unique Release #s, and now everything comes out the same. I know what I need to do to fix it, I am just not sure how. Now the 2 screenshots show my data. The 1st, the small one is where my formula will be. The second is where I will link to. I would like to add the JOIN Function to my existing formula in the Match section so it will Match another column, the one on the far left of the 2nd screenshot. This column named range is "Unique ID". It combines the name of each Phase and Release Number. However it is only on my 2nd sheet, as my sheet where the link will reside has only one record per Release So the JOIN must combine "Plan / Develop - Start" and the "Release" column, and the delimiter will be the "pipe" symbol. And it will Match withy the "Unique ID" column on the other sheet. I hope I have been clear in my explanation. Thanks so much for your help.

=IFERROR(INDEX({Start Date}, MATCH("Plan / Develop - Start", {Release Name}, 0)), "")

Sherry Fox

@Genevieve P. ,

Thanks for clarifying that!!!! I altered your formula slightly. The last Column Reference is actually Unique ID rather than Unique ID Helper. I had to do something different as I had received one of those messages that the name was already in use. Here is my final formula, and it works perfectly. Now I can complete the rest of my sheet with this formula, making slight adjustments.

=IFERROR(INDEX({Start Date}, MATCH(("Plan / Develop - Start | " + Release@row), {Unique ID}, 0)), "")

Paul Newcome

You would need to use a JOIN/COLLECT.

Sherry Fox

@Paul Newcome ,

Could you give me a little more help with this. I am not familiar with Collect. And I have only used JOIN all by itself. Using these 2 together, especially since they do not exist in Excel, has me a bit confused. So a little more help on this is greatly appreciated. Thanks in advance!!!

Paul Newcome

It would be something along the lines of...

=JOIN(COLLECT({Range To Join}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), "delimiter of choice")

Sherry Fox

@Paul Newcome ,

I am still having some issues understanding this one. I am uncertain about the "Range to Join" portion, as whenever I attempt to select anything, my formula automatically goes to "UNPASRABLE" and won't let me do anything. My formula so far is:

=JOIN(COLLECT({Unique ID}, {"{Release Name}}, "Plan / Develop - Start", {{Release}}, Release@row), " | ")

Genevieve P.

Heya! I'm happy to jump in here and clarify 🙂

Since your first sheet does not have the unique ID and you're looking to add data together to create it, I would actually recommend sticking with your original formula and simply use + to add your text and cell data together.

For example:

=IFERROR(INDEX({Start Date}, MATCH(("Plan / Develop - Start | " + Release@row), {Unique ID Helper}, 0)), "")

JOIN(COLLECT is a good formula, but is used when you have multiple data points to bring back from the second sheet, so an output would be "Row 1, Row 2" in the same cell, if that makes sense.

Let me know if using + worked for you! Keep in mind you'll need to use the exact same format you have in your second sheet (same number of spaces before the | and after).

Cheers,

Genevieve

Sherry Fox

@Genevieve P. ,

Thanks for clarifying that!!!! I altered your formula slightly. The last Column Reference is actually Unique ID rather than Unique ID Helper. I had to do something different as I had received one of those messages that the name was already in use. Here is my final formula, and it works perfectly. Now I can complete the rest of my sheet with this formula, making slight adjustments.

=IFERROR(INDEX({Start Date}, MATCH(("Plan / Develop - Start | " + Release@row), {Unique ID}, 0)), "")