Formula to populate multiple contacts into a cell based on role

Erin_R

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?

Genevieve P.

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

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.