Index collect from second sheet based on two criteria
After developing the below function, I later realized in addition to Participant@row, it needs a second criteria but I'm unsure how to update. The two criteria's are to match Participant@row with Program Year@row in both sheets before returning Milestones Submitted.
Thanks in advance for your help.
=INDEX(COLLECT({Milestones Submitted Tracker}, {Milestone Tracker 2}, Participant@row, {SEG Participant Tracker ADMIN Range 3}, Milestones@row), 1)
Answers
-
You would include a new range/criteria set within the COLLECT function using the same syntax that you have followed for the other two range/criteria sets.
-
Thanks Paul. I tried inserting according to what I thought lies in your answer but not getting it. Would you mind going a step further by typing out the new range/criteria in the function?
-
Hi @DustyF
If I am understanding it right, you want to add the new criteria for Year@row. Can you try below formula?
=INDEX(COLLECT({Milestones Submitted Tracker}, {Milestone Tracker 2}, Participant@row, {Milestone Tracker 2}, Year@row, {SEG Participant Tracker ADMIN Range 3}, Milestones@row), 1)
Thank You
Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ -
=INDEX(COLLECT({Range To Pull From}, {Criteria Range 1}, criteria 1, {Criteria Range 2}, criteria 2, {Criteria Range 3}, criteria 3), 1)
You just follow the same syntax you used for the first two range/criteria sets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!