INDEX & MATCH where the MATCH is based on a multi select dropdown?

sean59916

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

Jason Tarpinian

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

Ben Goldblatt

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

Jason Tarpinian

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

Ben Goldblatt

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

sean59916

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