Contact Lookup/Reference from Another Sheet

Hello,

I am trying to work out a formula that will raise a flag if any contacts in a column (Attendees) are listed on a separate sheet (VIP).

The attendees column could have multiple names listed, the VIP column will only have one name per row.

I am running into errors with the formulas that I have been trying, and not sure if I am even using the correct formulas. Have tried FIND, MATCH and they do not seem to do it.

Any ideas would be much appreciated.

Thanks,

Joe

Find more posts tagged with

Contacts

functionality

Accepted answers

Genevieve P.

Hi Joe,

What about using an IF(HAS formula? Since your Attendees sheet has multiple contacts, you will need to put this formula in the VIP sheet searching to see if the contact column in the other sheet HAS the specific contact in the VIP sheet. If it does, you could have this column be a Flag symbol, and raise the flag:

=IF(HAS({Attendees Sheet Contacts}, [VIP Contact]@row), 1, 0)

You would need to drag-fill this down it's column, so it checks each VIP per-row to see if the other sheet's column contains that contact. Here are some Help Center articles that I used:

Let me know if this works or if you have any questions.

Cheers,

Genevieve

All comments

Genevieve P.

Hi Joe,

=IF(HAS({Attendees Sheet Contacts}, [VIP Contact]@row), 1, 0)

You would need to drag-fill this down it's column, so it checks each VIP per-row to see if the other sheet's column contains that contact. Here are some Help Center articles that I used:

Let me know if this works or if you have any questions.

Cheers,

Genevieve

Joseph Noel

Thank you. That did work a little better than the way I figured it out. I figured out to use If(Contains( but the the has seems like a better fit.

Thank you again,

Joe

Genevieve P.

Ah yes! Contains works as well.

It's a small difference, but HAS is a function built specifically to search within Multi-Select columns (or multi-select contact lists).

Glad you got it working!

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