INDEX COLLECT, how to collect multiple values in a range when the criteria matches
I want to know all the employees those have salaries 13 using Index formula. Presently I am using the below formula, which provides only the first Employee getting 13 salary (in the cell belonging to 3rd column & 49th row)
=INDEX(COLLECT([Column2]42:[Column2]47, [Primary Column]42:[Primary Column]47, [Column3]49), 1)
Answers
-
Hey @Anup,
You can have it collect all the Employee Names and put them in 1 cell using JOIN/COLLECT like this:
=JOIN(COLLECT([Column2]42:[Column2]47, [Primary Column]42:[Primary Column]47, [Primary Column]@row), ", ")
Is this what you're hoping to do?
If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!
I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!
-
Hi @bisaacs, That is wonderful, the output is depicted in a single cell, whereas I need to get the result in successive rows or in a single column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!