Index Collect
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.
Best Answer
-
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
Answers
-
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
-
Thank you! This was very very helpful! Excellent.
-
No problem at all! I'm glad you found it helpful. 🙂
-
What do the colons in your example formula do?
I know it's been 2 years. 😐️
-
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:
Cheers,
Genevieve
-
Duh, of course! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!