Using INDEX/COLLECT to return multiple values

Jaye Casciano · 10/01/20

Looking for the proper way to return multiple values using INDEX/COLLECT. I'm assuming JOIN needs to be in there but can't get it to work! Both without and with JOIN, the formula still produces the first value only. This formula matches a project number from one sheet to another, and is supposed to return ALL of the resources assigned to that project (displayed in the source sheet as one resource per row, with all rows having the project number field filled out).

Here's what I'm trying to work with:

=JOIN((INDEX(COLLECT({source sheet resource name}, {source sheet project number}, [Project number]@row), 1)), ", ")

Note that this is in a contact column and ideally I'd like the returned values to be contacts, not flat text...but thinking that may not be possible. Grateful for any input!

Genevieve P. · 10/02/20

Hi@Jaye Tatone

The INDEX function is what's causing your output to bring only one value back. You can actually just jump rightinto a JOIN(COLLECT without INDEX at all. Try this:

=JOIN(COLLECT({source sheet resource name}, {source sheet project number}, [Project number]@row), ", ")

You are correct that currently this type of formula wouldn't be able to populate contacts; the JOIN(COLLECT will bring through the values as text. Keep in mind that if the same resource is assigned to the same project in two different rows you'd have their name brought through twice in this cell.

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P. · 10/02/20

Hi@Jaye Tatone

The INDEX function is what's causing your output to bring only one value back. You can actually just jump rightinto a JOIN(COLLECT without INDEX at all. Try this:

=JOIN(COLLECT({source sheet resource name}, {source sheet project number}, [Project number]@row), ", ")

You are correct that currently this type of formula wouldn't be able to populate contacts; the JOIN(COLLECT will bring through the values as text. Keep in mind that if the same resource is assigned to the same project in two different rows you'd have their name brought through twice in this cell.

Let me know if this works for you!

Cheers,

Genevieve

Tracey Katz · 10/26/20

@Genevieve PIs there a way to use JOIN-COLLECT to populate contacts? I am trying to do something similar but need to pull in contacts and not text. Or is there another function/function combo I can use to accomplish this?

Genevieve P. · 10/28/20

Hi@Tracey Katz

Would you be able to explain your process a little more, perhaps with screen captures of both sheets (but block out any sensitive data)?

There isn't a way to bring in multiple, separate contacts and join them into one Contact Column cell through a formula, but if you already have multiple contacts in one cell, there may be ways you can copy that full cell over to another sheet, or search within that cell.

mtk5200 · 12/09/21

Hi@Genevieve P.

Is it possible to use =Join(Collect to bring back multiple values but instead of a comma delimited list, have the values populate in the same column on separate rows? I've using join/collect in conjunction with a 'Text to Columns' option like excel and it doesn't appear to be an option.

I'm trying to pull into a sheet, from a separate sheet, all of the project names that are associated with one person's name. The catch is, multiple people can be assigned to one project so I can't use a report for this.

Thanks for your help,

Morgan

Genevieve P. · 12/13/21

Hi@mtk5200

The JOIN(COLLECT can only join data into onecellversus breaking out the data down rows.

However there may be a way we can do this. Do you know how many possible project names there could be? For example, up to 10?

We could use an INDEX(COLLECT formula to bring across the first match, then in the second row change the formula to look for thesecondmatch, and so on.

For example:

=INDEX(COLLECT({Project Names}, {People Assigned}, HAS(@cell,[email protected])), 1)

The 1 at the end tells the formula to bring back the first row.

Then in your second row for that user, update it to:

=INDEX(COLLECT({Project Names}, {People Assigned}, HAS(@cell,[email protected])),2)

If you know the max number of Projects that could be assigned to someone, you could set up your sheet ahead of time and have the numbers in the cell. Then you can reference the cell instead of writing 1 or 2. Here's an example of what I mean:

Screen Shot 2021-12-13 at 1.01.19 PM.png

Cheers,

Genevieve

Marcin · 03/14/22

Hi@Genevieve P.

I'm using your last example which works fine in the give sheet. Trying to modify that to reference another sheet but getting unparseable

Surely I'm wrongly inserting reference to another sheet. Any hint what's wrong with references here?

=INDEX(COLLECT({Integrated Launch Plan Range 1}[Task Name]:[Task Name], {Integrated Launch Plan Range 1}[Priority Focus Flag]:[Priority Focus Flag], HAS({Integrated Launch Plan Range 1}@cell, "Local Priority")), 3)

Best

Marcin

Genevieve P. · 03/14/22

Hi@Marcin

There are a couple of syntax errors here.

The first thing you list in the COLLECT function is the column you want to bring data back from. Then you list each Column and Criteria after it.

So in your case,Range 1should be the Column to bring back data.

=INDEX(COLLECT({Integrated Launch Plan Range 1}

Then if you're looking for a specific Task Name,Range 2would be the Task Name column in the other sheet:

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch PlanRange 2}

And if you're searching for the Task Name in the cell in this same row where you're writing the formula, the reference is [Task Name]@row

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2},[Task Name]@row

Next we list the multi-select column, which it sounds like is called "Priority Focus Flag"in the other sheet.This would be range 3:

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2},[Task Name]@row, {Integrated Launch PlanRange 3}

And then what you're looking for in that multi-select would be "Local Priority", you can use HAS(@cell to search for it in the previously stated range:

=INDEX(COLLECT({Integrated Launch Plan Range 1}, {Integrated Launch Plan Range 2},[Task Name]@row, {Integrated Launch PlanRange 3},HAS(@cell, "Local Priority")), 1)

You always need a 1 at the end because that tells the INDEX function whatrowto bring back. Does that make sense?

如果从你这个公式不工作,请screen captures of your current source sheet and the sheet where you're writing the formula, identifying what columns you want to Match and what you want to Bring Back, but please delete out sensitive data.

Cheers!

Genevieve

Using INDEX/COLLECT to return multiple values

Best Answer

Answers

Help Article Resources

Categories