Contact column to return multiple email addresses

Jared F

Good morning, I have a contact column, then an email address column where I use a Vlookup function to go to another sheet and pull the users email address from. However, when I enter multiple contacts I want a formula to return ALL those email address back to my email address column.

I know VLOOKUP isn't it, and I tried JOIN(COLLECT), however, retults come up blank when I have two contacts in the range. Help!

Mike TV

@Jared F

Can you show how you have the column formatted for multiple email contacts on your sheet? Use fake email addresses though so you don't expose someone's email address. If it's a multi-select drop down column, then I don't believe it's possible. However, if you have them separated with a semicolon or something then it could be difficult but do-able.

Jared F

Here is the column we are pulling from. The reference sheet is 1 email address per cell, but that isn't the issue as my formula works if this column only has one name. This is a Contact column type with multiple selection option.

Mike TV

@Jared F

Ok so this is possible but it'll take a lot of formulas and work to get there. I'll point you in the right direction (hopefully) but you'll have to do the leg work on putting all the pieces together. Maybe someone else will have a better solution than this one as well if you give it some time.

Basically, your contact column with multiple contacts has the contacts separated by commas in the background. If you do test formula such as =FIND(",", Contact@row) then you'll see it return the position value of the first comma which would be directly after the first person's final letter in their last name.

With that value you can use a formula such as =LEFT(Contact@row, 19) and if 19 were the value the above formula returned for the first comma position, then it would give you that first person's name which you can then VLOOKUP their email address with.

To find the next person's name, you'd want to use =FIND(",", Contact@row, 20) and then it will tell you where the position is for the 2nd comma.

You'll then need to use LEFT and SUBSTITUTE to cut out the first person's name to help gather the 2nd.

To be honest, this is all probably more hassle than it's worth unless you absolutely have to get this working...but it is do-able. It depends on how badly you want it to work like this automatically.

Maybe there's a more elegant solution someone will come up with for you in the meantime.