Pulling Multiple Values from One Sheet Based on Multiple Values in a Second Sheet

Brian McElligott

I have two sheets.

Sheet 1 contains a multi-select location list.

Sheet 2 contains contacts for each location on sheet 1

For sheet one, I need a list of all related contacts (from sheet 2) that correspond with the list of locations (sheet 1).

I have tried to use a Join-Collect function but with no success.

Is there an issue with Join/Collect functions with multi-select dropdown columns?

Suggestions?

jmyzk_cloudsmart_jp

Hi @Brian McElligott

Unfortunately, creating a multi-select contact list with a formula is impossible.

https://community.smartsheet.com/discussion/111998/formula-for-contact-list-to-show-multiple-values-from-source-sheet

So, a workaround is to create multiple (single-select) contact lists.

https://app.smartsheet.com/b/publish?EQBCT=dcb09b37dd6d43c099ccc2dadc3a49e8 (Sheet 1: Demo Solution Sheet)

https://app.smartsheet.com/b/publish?EQBCT=d8ea389300134710bf0f039d12361ef3 (Sheet 2: Demo Location-Contacts Sheet)

First, we need to split multi-select location values to separate values.

We need to use the Text functions like MID, SUBSTITUTE, and FIND for the task.

[Substituted] ="~" + SUBSTITUTE([Locations (Multi-Select)]@row, CHAR(10), "~") + "~"
[Location 1] =IFERROR(MID(Substituted@row, FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 1)) + 1, FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 2)) - FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 1)) - 1), "")
[Location 2] =IFERROR(MID(Substituted@row, FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 2)) + 1, FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 3)) - FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 2)) - 1), "")
[Location 3] =IFERROR(MID(Substituted@row, FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 3)) + 1, FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 4)) - FIND("^", SUBSTITUTE(Substituted@row, "~", "^", 3)) - 1), "")

Then, using those location values, we can use the INDEX(MATCH()) functions to get the Contact value.

[Contact 1] =IF(ISTEXT([Location 1]@row), INDEX({Sheet2_Email}, MATCH([Location 1]@row, {Sheet2_Location}, 0)))
[Contact 2] =IF(ISTEXT([Location 2]@row), INDEX({Sheet2_Email}, MATCH([Location 2]@row, {Sheet2_Location}, 0)))
[Contact 3] =IF(ISTEXT([Location 3]@row), INDEX({Sheet2_Email}, MATCH([Location 3]@row, {Sheet2_Location}, 0)))
[Multiselect Dropdown List] =JOIN([Contact 1]@row:[Contact 3]@row, CHAR(10))

As I explained at the top, you can not create a multiple-contact list, so I created multiple drop-downs for demo purposes. However, this is useless for workflow send-alert, request-update, and approval request automations.

Still, I can use the multiple (single-select) contact lists for contact-related automation.