INDEX & MATCH where the MATCH is based on a multi select dropdown?
Hi -
I have been trying to work this out, read through a number of threads, but, cant see this question directly answered.
Scenario:
I have 1 sheet that has a list of tools and another sheet which is used to record who those tools have been handed out to by using a multi select dropsown column. What I would like to be able to do is to have the person's name against the tool.
I have tried using: =INDEX({Who}, MATCH(HAS(@cell, Tool1), {Tool}, 0)) and different variants, but, i can't get it to work!
For example:
The tool column on the tracker sheet is using a multi select dropdown.
Is there a way to do this?
Thanks for any help,
Sean
Best Answers
-
If I'm understanding the request correctly, for the first picture you show, you'd want "Hammer (123)" and "Drill (456)" to show "Sean Williams" in the who column? If that is correct, for my solution, I am also assuming that each tool has a unique ID or bar-code number and can only be in use by one person at a time. To get my solution to work, I created a helper column to just number the rows. Then you can use a SUMIF with a CONTAINS to find the row number to use as your row # in the index formula.
=INDEX(Who:Who,SUMIFS([Primary Column]:[Primary Column], Tool:Tool, CONTAINS([Tool (Other Sheet)]@row, @cell)))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Great work Jason!
@sean59916 Using the same logic that Jason used for his formula, you could use this for your cross-sheet formula:
=INDEX({Who}, SUMIFS({Row Number}, {Tool}, CONTAINS(Tool@row, @cell)), 0)
Source sheet
Destination sheet
I hope this helps!
Ben
Answers
-
If I'm understanding the request correctly, for the first picture you show, you'd want "Hammer (123)" and "Drill (456)" to show "Sean Williams" in the who column? If that is correct, for my solution, I am also assuming that each tool has a unique ID or bar-code number and can only be in use by one person at a time. To get my solution to work, I created a helper column to just number the rows. Then you can use a SUMIF with a CONTAINS to find the row number to use as your row # in the index formula.
=INDEX(Who:Who,SUMIFS([Primary Column]:[Primary Column], Tool:Tool, CONTAINS([Tool (Other Sheet)]@row, @cell)))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Great work Jason!
@sean59916 Using the same logic that Jason used for his formula, you could use this for your cross-sheet formula:
=INDEX({Who}, SUMIFS({Row Number}, {Tool}, CONTAINS(Tool@row, @cell)), 0)
Source sheet
Destination sheet
I hope this helps!
Ben
-
Thanks both - It's always good to get a different perspective when spending a ton of time staring at the issue for some time. Appreciate the help.
Sean
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!