Formula to populate multiple contacts into a cell based on role

Erin_R
Erin_R ✭✭
edited 12/09/19 in Formulas and Functions

I used the following Match/Index formula to populate the "Assigned To" column of my sheet based on the "Role" column. I have a look up table at the top of my sheet. 

=IFERROR(INDEX([Assigned To]$6:[Assigned To]$20, MATCH(Role@row, Role$6:Role$20, 0)), "Needs Role")

There are many tasks on my sheet which are assigned to multiple roles, so I need to update the "Assigned To" column with multiple contacts as well. Does anyone have a formula that would accomplish this?

Capture_6.PNG

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Erin,

    It looks like the formula you have set up should work just fine, however based on your screen capture, what needs to be updated is the table that is referenced. With Multi-Select columns, an Index/Match formula will look at one cell to see if all of the values in that cell match an identical cell, containing the exact same values.

    For example, if your table has two values listed in two separate rows/cells (such as "Marketing" and then "Operations"), your current formula would not find a match. You would need to update your table to include all the possible combinations of multiple values (such as a row that has both "Marketing" and "Operations", and put both contacts in that row's contact cell).

    Like so:

    Marketing - Jen

    Operations - Mark

    Marketing, Operations - Jen, Mark

     

     

    You will also need to make sure that your Contact column is set up to be able to list multiple contacts (to do so, double click on the column name at the top the sheet, then check the box that says "Allow multiple contacts per cell.")

    If you have done this and your formula is still not returning the expected values, it would help to see a screen capture of the table being referenced.

    Thanks!

    Genevieve

  • Danielle Roach
    Danielle Roach ✭✭✭✭

    Thanks Genevieve - That's a nightmare with as many people as I have for my contacts and roles in any specific sheets. Oof.

    Hopefully SS comes up with something.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!