INDEX MATCH on multiple value cell



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



Best Answer


  • 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
    Stephanie Allison ✭✭✭
    Answer ✓

    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
    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
    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
    Kevin7859 ✭✭✭✭

    Is this what you were talking about:

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Kevin7859 Close.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!