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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you! This was very very helpful! Excellent.
-
No problem at all! I'm glad you found it helpful. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Duh, of course! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!