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

  • Genevieve P.
    Genevieve P. Employee
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!