Can INDEX / MATCH join more than one item?


The INDEX / MATCH works when I want to match each name with one team... but if I have all three names in one cell, can I get a return of all three teams associated with each name?



  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 10/21/22

    Try whit this instead;

    Change the type of column for your formula to "Dropdown Multipick"

    With a

    =if([Name]@row="","",JOIN(COLLECT(Team:Team, [Name]:[Name], HAS([Name]@row, @cell)), CHAR(10))

    Maybe you'll need to separate the name and TEAM reference from the column where you have multiple name.

    =if([Name]@row="","",JOIN(COLLECT(Team:Team, [NameREF]:[NameREF], HAS([NameList]@row, @cell)), CHAR(10))

  • Berto D
    Berto D ✭✭
    edited 10/21/22

    It worked! Thanks, Christian!

    Although Smartsheet changed the formula somewhat to the following:

    =IF(Names@row = "", "", JOIN(COLLECT(Team:Team, Names:Names, HAS(Names@row, @cell)), CHAR(10)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!