Index Match with a multi-select dropdown

I am trying to write a formula that looks at a multi-select column to pull email addresses from a reference sheet. With my current formula it only works when there is one value in the multi-select column.

=JOIN(COLLECT({Reference Sheet | Contact Q2}, {Reference Sheet | BID}, HAS(@cell , [Sites to be included]@row )), "/")

Multi-select column:

Reference Sheet:

Find more posts tagged with

Formulas

Accepted answers

Darren Mullen

@Nick0000

Since you're comparing the value in the BID column to that in the multi- select you can reverse your HAS() conditions:

=JOIN(COLLECT({Reference Sheet | Contact Q2}, {Reference Sheet | BID}, HAS( [Sites to be included]@row ,@cell )), "/")

The range is the [Sites to be included]@row cell and the search value is the value in the cell being evaluated in the References sheet BID range.

All comments

Darren Mullen

@Nick0000

Since you're comparing the value in the BID column to that in the multi- select you can reverse your HAS() conditions:

=JOIN(COLLECT({Reference Sheet | Contact Q2}, {Reference Sheet | BID}, HAS( [Sites to be included]@row ,@cell )), "/")

The range is the [Sites to be included]@row cell and the search value is the value in the cell being evaluated in the References sheet BID range.

Nick0000

Thanks for the help Darren! Would you know if the returned values would be able to be used in an approval notification?

Darren Mullen

@Nick0000 I assume you are trying to get the email addresses into a contact column and use that to send out a notification?

Smartsheet doesn't let you concatenate multiple email addresses like that into a multi contact column.

If you are pulling 1 email address, it works.

now, you can pull in data from a multi contact cell on one sheet to another multi contact cell on another sheet (up to 20 contacts)

This is useful if you have a number of contacts associated with a department, then you want to pull in all contacts associated with a department. You can have the contacts in a multi contact column, and a column with the department name, then reference that in other sheets.

But, if you are trying to join collect email addresses, you can't use those in a contact column.

Nick0000

Thanks Darren! Do you know if the return values of "test@email.com/test2@email .com" would be able to in a notification alert?

Nick0000

Thanks Darren. That makes sense. Going with your example, if I needed to pull the contacts from two different departments into a contact column would that be possible?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.