Index and match using dropdown, multi select column.

Shane.S

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

Genevieve P.

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

Genevieve P.

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

Shane.S

That worked perfectly!!! Thank you!

Valerie Olson

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.

Genevieve P.

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

Valerie Olson

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.

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