Index Match for Multiple Options

Kate H

I want to set a formula that will index match the value(s) of a cell with a reference list of group name and associated contact information. Is this even possible?

I have Sheet 1: list where each row has an entry that states in one cell, multiple group names

I have Sheet 2: list of contacts by group name

I want to have each row in the Impact Group column of Sheet 1 find the impacted group name in Sheet 2 and identify the contact associated with that group and add it to the contact list in line on Sheet 1. I used Excel to quickly type out the general idea. I'm trying to create the formula to generate Sheet/Book 2 Column C below.

. Later I'd like to set an automated contact of the contacts in that list for all in the cell with the collected necessary contact names.

I've successfully created this for a single group value, so the Data 3 row works to find jane doe's email, but the multiple groups impacted, I'm guessing it looks up a group that has both names and doesn't find it. I'd like it to compute this decision for each input and concatenate them together into the contact cell. Is this possible?

Paul Newcome

We cannot currently use formulas to output multiple usable contacts into a single cell. You would need to use a JOIN/COLLECT type of formula to bring all of the contacts together then use one of the premium add-ons listed in the below article:

https://community.smartsheet.com/discussion/123433/combine-emails-into-a-multiple-contact-column-multi-contact-column