Index Match with multiple results?

Kristin Stoddard

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?

Thanks!

Kristin

David Joyeuse

Hi @Kristin Stoddard

You can only return one value with INDEX/MATCH. But you can return more than one using a JOIN/COLLECT function.

Which would gives you something like:

=JOIN(COLLECT({Evaluations Range}, {Supervisor Range}, [Supervisor]@row), CHAR(10))

CHAR(10) is a function to go to the next line, between two evaluations.

You can also add more criteria to the COLLECT function.

Hope it helped!

David Joyeuse

Hi @Kristin Stoddard

You can only return one value with INDEX/MATCH. But you can return more than one using a JOIN/COLLECT function.

Which would gives you something like:

=JOIN(COLLECT({Evaluations Range}, {Supervisor Range}, [Supervisor]@row), CHAR(10))

CHAR(10) is a function to go to the next line, between two evaluations.

You can also add more criteria to the COLLECT function.

Hope it helped!

Kristin Stoddard

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

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))

CAS the CSA

Can't delete. I withdraw my question, I had the ranges swapped.