INDEX MATCH on multiple value cell

Hi,

I have a column that can be filled from multiple items from dropdown list.

On a second column, i'd like to return the matching items to those inserted in a first column.

To achieve this, i have a second sheet with each individual item and it's match.

Unfortunately when I try the index match formula, it only work when only 1 item is filled in column 1. As soon as i have more, i assume column 2 formula looks for the combination of both items instead of looking for them separately and fails to find a match (rightfully so).

Is there an alternative solution for those matches or should i give up altogether ?

Thank you in advance for the support

Kindly,

Amandine

Find more posts tagged with

Sheets

Formulas

Accepted answers

Stephanie Allison

I'm glad I could help Amandine. I suspect the majority of my problem lies in using contacts. Unfortunately, these drive my notifications....

That post by Zeb Loewenstein is a fantastic resource.

All comments

Stephanie Allison

I am working on the exact same problem.

In sheet 1, I have a contact column that can have multiple items, the travelers name column. Using sheet 2, I would like to have the job title for each traveler to populate.

Sheet 1

Sheet 2 - Employee Email is also a contact column.

I found a very useful community post, Join Collect formula for multiple values — Smartsheet Community

and based on that tried this formula: =JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, CONTAINS(@cell, [Traveler(s) Name, If known (select)]@row)), ",") however I'm not getting anything in the column, even if there is only one contact in the traveler column.

When I use this formula, =IFERROR(INDEX(COLLECT({Employee List PCN}, {Employee List Employee Name}, [Traveler(s) Name, If known (select)]@row), 1), "No Match Found") it works for single employee fields, but I get a "No Match Found" on those with multiple employees. When I tried index match rather than index collect, I would get the information for the first employee, but not all.

So. I can't answer your question, but I feel your pain!!

Amandine

Hi Stephanie !

Thank you very much for sharing ! Turns out the solution you tested worked for me :)

Sometimes I have difficulties with pulling out information when column format is in contact, this could be the reason why it is not working as well.

In the formula you shared, the item in bold, is sometime pulling emails or mails correct ?

=JOIN(COLLECT({Employee List Job Title}, {Employee List Employee Name}, CONTAINS(@cell, [Traveler(s) Name, If known (select)]@row)), ",")

Thank you again for the help, hope you find a solution soon :)

Stephanie Allison

I'm glad I could help Amandine. I suspect the majority of my problem lies in using contacts. Unfortunately, these drive my notifications....

That post by Zeb Loewenstein is a fantastic resource.

Kevin7859

I tried the Index Match and got an invalid argument error.

I use the index collect, but with multiple values in the look-up cell it returns an #Invalid Value error.

I am using a dropdown for Instructor name based on Contact list with allowing multiple values for several instructors in the class. I have them listed in a contact list and am trying to pull their full names from the table allowing them to see their classes in a dashboard.

=INDEX(COLLECT({Instructor Name}, {Instructor Email}, Instructor@row), 1, 1)

Any Help would be beneficial.

Paul Newcome

The thing to remember with contact type columns is that the CONTAINS function doesn't always play nice. Try a HAS function in the JOIN/COLLECT instead.

Kevin7859

Is this what you were talking about:

=Join(COLLECT({Instructor Name}, {Instructor Email}, HAS(Instructor@row,"")), 1, 1)

Paul Newcome

@Kevin7859 Close.

=Join(COLLECT({Instructor Name}, {Instructor Email}, HAS(@cell, Instructor@row)), "delimiter of choice")

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.