Contact Lookup/Reference from Another Sheet

Joseph Noel

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

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:

• HAS function
• Cross Sheet References
• @row function

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

Cheers,

Genevieve

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:

• HAS function
• Cross Sheet References
• @row function

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!