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?
Thanks!
Kristin
Best Answer
-
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!
Answers
-
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!
-
OMG this is beautiful!!! Was driving myself nuts trying to figure this out. Thanks so much @David Joyeuse !
-
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))
-
Can't delete. I withdraw my question, I had the ranges swapped.
-
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.
-
@Robin H 77 Hi Robin, I had this same issue before. At the end of your formula, instead of ," ") replace that with , CHAR(10)
-
@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?
-
@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
-
Thanks - I'm willing to give it a shot! My list has 23 rows 🤪
-
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))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!