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.

Robin H 77

I am trying to use this formula to return 2 values, from different rows, from the same reference sheet, so when the Workstream column has more than one value, the Owner column is populated with the associated Owner Name for each of the Workstream values. I have tried modifying this statement multiple ways, but all I'm getting is either null value or I get the name of the person in the row just under the value(s) I should have. I've asked a few coworkers to help me, and they couldn't figure it out either.

Adriana Apodaca

@Robin H 77 Hi Robin, I had this same issue before. At the end of your formula, instead of ," ") replace that with , CHAR(10)

Robin H 77

@Adriana , thanks, but I am still getting a blank whenever I have more than one value in the Workstream column. It does work if I only have one value, so that's progress! Any other thoughts?

Adriana Apodaca

@Robin H 77 Apologies, I read a bit too quickly and glossed over your first range having multiple options needing collection. I am not sure how long your Workstream/Name lists are but it may be easier to do nested IF statements- ex:

=IF(HAS([workstream]@row, "HRBP"), index({nld owner name range}, match("HRBP", {nld workstream range},0) + CHAR(10)) + IF(HAS([workstream]@row, "HR/Legal"), index({nld owner name rnage}, match("HR/Legal", {nld workstream range},0) and so on

I do not have much knowledge working with Contact columns but this is would be my next thought

Robin H 77

Thanks - I'm willing to give it a shot! My list has 23 rows 🤪

Robin H 77

I got this to work with one HAS statement, but once I added the second one I got an INVALID DATA TYPE error:

=IF(OR(HAS(Workstream@row, "HR/Legal"), INDEX({NLD Owner Names 2}, MATCH("HR/Legal", {NLD Workstream Range 2}, 0))), HAS(Workstream@row, "HRBP"), INDEX({NLD Owner Names 2}, MATCH("HRBP", {NLD Workstream Range 2}, 0))))