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.

Screen Shot 2021-04-29 at 9.10.27 AM.png

In sheet two, I have a column with the list of individuals and a column where I want their current assignment returned.

Screen Shot 2021-04-29 at 9.21.36 AM.png

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

Find more posts tagged with

Accepted answers

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

All comments

Genevieve P.

Hi @Shane.S

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

=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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.