Index Match with multiple results?


We are tracking year end evaluations submitted by the supervisors for their staff, but when I use Index/Match, the only value returned is the first staff member reviewed by that supervisor. Is there a way to find and combine all results by supervisor?



Best Answer


  • Kristin Stoddard

    OMG this is beautiful!!! Was driving myself nuts trying to figure this out. Thanks so much @David Joyeuse !

  • RAllen
    RAllen ✭✭

    Great solution!

    Added to that - I was looking up project numbers across other tickets, so I remove the matching (Same) Project number using a substitute, and then another substitute to look for any LF/LF and replace with a single LF. Unfortunate Smartsheets doesn't support Regex- that could find doubles, leading or training!

    =SUBSTITUTE(SUBSTITUTE(JOIN(COLLECT([Primary Column]:[Primary Column], [Proj. Num.]:[Proj. Num.], [Proj. Num.]@row), CHAR(10)), [Primary Column]@row, ""), CHAR(10) + CHAR(10), CHAR(10))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!