Index and match using dropdown, multi select column.
Hello,
I want to index and match off of a dropdown, multi-select column. If there are multiple selections in the dropdown I would like the project name to appear next to each individual name so that I can track assignments by the individual.
In sheet one, I have the project name and assignee names. The assignees are multi-select.
In sheet two, I have a column with the list of individuals and a column where I want their current assignment returned.
I am aware of how to get a return for one person using =IF(NOT(ISBLANK(Person@row)), INDEX({Test Sheet 2 Range 2}, MATCH(Person@row, {Test Sheet 2 Range 1}, 0)), " ")
How can I have the project name return for multiple matches on separate rows? In this case, "taco" would show for Sue, John and Karen.
Thank you
Best Answer
-
Hi @Shane.S
Since your column is a Multi-Select column, you'll want to see if the cell HAS a specific person selected. The HAS function will look through each multi-select option, versus looking at the cell as a whole.
Since we're using HAS, I personally prefer having this in an INDEX(COLLECT formula instead.
Try this:
=IF(NOT(ISBLANK(Person@row)), INDEX(COLLECT({Test Sheet 2 Range 2}, {Test Sheet 2 Range 1}, HAS(@cell, Person@row)), 1), " ")
Keep in mind this is only finding one match... so if Jim, Karen, or and of your other People are listed more than once in the Multi-Select column, you'll only see one value returned. If you want a list of all the projects associated with that person, you may want to use a JOIN(COLLECT instead, like this:
=IF(NOT(ISBLANK(Person@row)), JOIN(COLLECT({Test Sheet 2 Range 2}, {Test Sheet 2 Range 1}, HAS(@cell, Person@row)), " / "), " ")
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Shane.S
Since your column is a Multi-Select column, you'll want to see if the cell HAS a specific person selected. The HAS function will look through each multi-select option, versus looking at the cell as a whole.
Since we're using HAS, I personally prefer having this in an INDEX(COLLECT formula instead.
Try this:
=IF(NOT(ISBLANK(Person@row)), INDEX(COLLECT({Test Sheet 2 Range 2}, {Test Sheet 2 Range 1}, HAS(@cell, Person@row)), 1), " ")
Keep in mind this is only finding one match... so if Jim, Karen, or and of your other People are listed more than once in the Multi-Select column, you'll only see one value returned. If you want a list of all the projects associated with that person, you may want to use a JOIN(COLLECT instead, like this:
=IF(NOT(ISBLANK(Person@row)), JOIN(COLLECT({Test Sheet 2 Range 2}, {Test Sheet 2 Range 1}, HAS(@cell, Person@row)), " / "), " ")
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That worked perfectly!!! Thank you!
-
Similar to this, if I have a set of skills listed in my source sheet for an inidivual and have a target sheet that has a streamlined list of skills that I would want to match a person to can I use JOIN(Collect( to match to a multi-select column? For instance I want to find an individual that both has project management skills and sustainability experience. Both the columns are multi-select drop downs and have the exact same selections. I want to be able to pull all people from my source sheet that have both of these skills.
-
Yes! If I'm understanding you correctly, you should be able to use a JOIN(COLLECT to do exactly this.
In your instance, you'd want to search to see if your multi-select column HAS value 1 AND HAS value 2.
Try something like this:
=JOIN(COLLECT({Column with people}, {Multi-Select Column}, AND(HAS(@cell, "Project Management"), HAS(@cell, "Sustainability")))
Let me know if this works for you! If not, it would be helpful to see a screen capture of each sheet, but please block out sensitive data.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Genevieve. I am still getting an "invalid operation" though. If the "@cell, "Project Management" is actually a multi select column with multiple skills we are seeking how would I do that?
-
The multi-select column would be the cross-sheet range: {Multi-Select Column}
Then we use HAS because it's multi-select, to search through each individual cell (using @cell) to see if each cell HAS the text "Project Management" along with other selections.
So:
=JOIN(COLLECT({Column with people}, {Multi-Select Column}, AND(HAS(@cell, "Skill 1"), HAS(@cell, "Skill 2")), " / ")
Keep in mind this is looking to see if the person has both skills, since we're using AND.
Can you copy/paste the formula you're using? Is it possible that the columns you're referencing have a formula error in any of their cells? This would create a domino effect and bring the error up into this formula as well.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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!