Index Collect

Reza Djangi

Hi all,

I wanted to see if anyone could give an ELI5 version of how index collect works and some use cases. The only youtube video I found was just an example without explanation on the syntax and why/how it works. I already know how index match works, but the index collect is throwing me off.

Thank you so much for your help.

Genevieve P.

Hi @Reza Djangi

Essentially an INDEX(COLLECT is a way to bring back one value based on multiple criteria.

The COLLECT function acts like a filter, so you list all of your columns and criteria in a row, and at the very front you have the column to bring a value back from.

For example:

=INDEX(COLLECT({Column To Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), 1)

You need the 1 at the end of the INDEX function to identify what row to bring back. In this instance, the first match for all those criteria.

If you may have multiple matches for the same criteria, you can use JOIN(COLLECT

=JOIN(COLLECT({Column To Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

Then this will bring your multiple values together in one cell. The examples I have above are using cross-sheet references, but you could do this in-sheet as well:

Let me know if I can clarify anything further!

Genevieve

Genevieve P.

Hi @Reza Djangi

Essentially an INDEX(COLLECT is a way to bring back one value based on multiple criteria.

The COLLECT function acts like a filter, so you list all of your columns and criteria in a row, and at the very front you have the column to bring a value back from.

For example:

=INDEX(COLLECT({Column To Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), 1)

You need the 1 at the end of the INDEX function to identify what row to bring back. In this instance, the first match for all those criteria.

If you may have multiple matches for the same criteria, you can use JOIN(COLLECT

=JOIN(COLLECT({Column To Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

Then this will bring your multiple values together in one cell. The examples I have above are using cross-sheet references, but you could do this in-sheet as well:

Let me know if I can clarify anything further!

Genevieve

Reza Djangi

Thank you! This was very very helpful! Excellent.

Genevieve P.

No problem at all! I'm glad you found it helpful. 🙂

Pinal_Fairgrounds

https://community.smartsheet.com/discussion/comment/309626#Comment_309626

What do the colons in your example formula do?

I know it's been 2 years. 😐️

Genevieve P.

Hey @Pinal_Fairgrounds

Haha no problem with the years, I'm happy to answer!

In Smartsheet, when you reference a column in the same sheet, you use [square brackets]. Then after the name of the column, you identify the row you want to reference.

So if I was looking at Row 5 in the Type of Fruit column, it would look like this:

=[Type of Fruit]5

Ok, so that's great for a single cell reference. But what if I want to reference a range, from Row 5 to Row 10?

This is where the colon comes into place. I know that row 5 for that column would be [Type of Fruit]5 and row 10 for the same column would be [Type of Fruit]10... how do I tell it to reference all the cells in between? The colon!

=[Type of Fruit]5:[Type of Fruit]10

Ok, but now what if I want the entire column without locking it to specific rows? (So new rows inserted are immediately in my formula). Well then all we need to do is take out the numbers!

=[Type of Fruit]:[Type of Fruit]

This says, look at the first row (whatever number that is) until the last row of data (whatever number that is) for the entire Type of Fruit column. Does that make sense?

Here's more information:

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

Cheers,

Genevieve

Pinal_Fairgrounds

Duh, of course! Thank you!