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)



  • bisaacs
    bisaacs ✭✭✭✭✭

    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!

  • Anup
    edited 07/03/24

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!