Index collect from second sheet based on two criteria

DustyF

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)

Paul Newcome

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.

DustyF

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?

Amit Wadhwani

https://community.smartsheet.com/discussion/93381/index-collect-from-second-sheet-based-on-two-criteria

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

https://www.linkedin.com/in/amitinddr/

Paul Newcome

=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.