MATCH multi-dropdown to another column text

Hi! I'm trying to match a column containing job titles to a dropdown list containing the same titles, so that when certain titles are selected in "Audience1", it will return an email address when there's a match.

I want "Distro List" to populate with "Business Email Information Email Address" where "Title" matches whatever titles are selected in "Audience".

I've tried various combinations of INDEX, MATCH, and HAS with no success. Thank you in advance!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You would need a JOIN/COLLECT, but...

There is currently no way to join multiple emails into a string that will be useable for "Current User" filters or automations. It is simply a text string.

Austin Smith

Try this

=IF(CONTAINS([Title], [Audience]), [Business Email Information Email Address], "")

Austin Smith

Sorry, misunderstood that you wanted it compiled. Yes, Paul is correct about that part.

If you put the Contains formula in the Distro list column as a column formula and lock the reference to the Audience cell, you could come up with a report that filters based on non-blank cells to give you a quick list. Then select the cells with emails in them from the report and paste into your To: field in your email. It works just fine.

Jenn Anderson

@Austin Smith you were correct, I just wanted the email returned for that row. I ended up using:

=IFERROR(INDEX(COLLECT([Business Email Information Email Address]@row, Title1, HAS(@cell, Title@row)), 1), "") because CONTAINS was picking up titles where it contained part of the job title but not all (example, EVP Operations was being counted as VP Operations).

Austin Smith

@Jenn Anderson

Nice! Don't know that I've run across that that pushed that issue out. Thanks for the heads up.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.