Using Match Function with Multiple Contact Column

Hello, I need to lookup a value in a table when a specific contact matches. I tried using match/index, however the column containing the contacts in the lookup table allows multiple contacts which I am unable to determine how to use match with multiple contact listings.

The use case is as follows: Send an email alert to all assigned managers in a particular unit when a ticket is opened by someone in their unit.

Any assistance is appreciated,

Thanks!

Find more posts tagged with

Contacts

Formulas

Groups and Group Management

Accepted answers

All comments

Paul Newcome

It is currently not possible to use a formula to populate multiple contacts.

Ross Ihrig

Thanks Paul,

I may have been unclear, I am not trying to populate a multiple contacts field, but I am trying to lookup a value if a contact exists in a multiple contact field. The HAS function works well to tell me that the name exists in the column, but I am unable to determine how to combine this with a Match/Index or VLookup.

Thanks!

Paul Newcome

Try an INDEX/COLLECT instead.

=INDEX(COLLECT({Range to pull from}, {Contact Column}, HAS("Contact", @cell)), 1)

Steve Reed

@Paul Newcome I tried this formula in a similar use case but it is not working. I am hoping you can help figure this out. I have two sheets--"Resource List" and "Crew/Resource Assignment" where I am trying to pull data from a multi-value column.

Resource List: The Team Members on the other sheet are in a multiple contact list. On this sheet, I want to auto-populate the Crew # column from the other sheet. I am showing the two formulas I have tried to accomplish this. Thoughts?

SS Comm-Data from Multiple Contacts Column 220316-1.png

Crew/Resource Assignment: Here I want to pull the skills listed on the "Resource List" sheet for each team member. For example, the first two Names on the Resource List are assigned to Crew #1. So the skills here should show A, B, C, D (and a second C, if necessary). I am hoping that this can be done similarly to the previous solution.

SS Comm-Data from Multiple Contacts Column 220316-2.png

I appreciate any help that anyone can lend here. It is baffling me!

Steve

Paul Newcome

@Steve Reed You are going to want to work with a JOIN/COLLECT instead.

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.