Index and match using dropdown, multi select column.

Shane.S
Shane.S
edited 04/29/21 in Formulas and Functions

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

  • Genevieve P.
    Genevieve P. Employee
    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

  • Genevieve P.
    Genevieve P. Employee
    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

  • 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.

  • Hi @Valerie Olson

    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?

  • Genevieve P.
    Genevieve P. Employee
    edited 08/25/21

    Hi @Valerie Olson

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!