Can you match between multi select dropdowns?

Josh Reilly

I'm trying to match values between two multi drop downs. In one sheet there are rows that are associated with multiple groups, in the summary sheet are the groups that are selected. Is there a way to write a formula that will look at the summary values in the dropdown and see if any of those values exist in the second sheet's dropdowns?

I've tried to use the HAS function to populate, but if there are more than one value in the summary sheet, it comes back as false.

=IF(HAS([Group Name]@row, {ProjectGroup}), 1, 0)

Main Sheet

Summary Sheet

Any help would be greatly appreciated

Genevieve P.

Hi @Josh Reilly

Would you be able to describe what your process/purpose for this is a little more?

From what I can see it looks like you have your Range and Criteria the wrong way around. In a HAS statement you first list the range to look through, then the criteria it's looking for. Try this:

=IF(HAS({ProjectGroup}, [Group Name]@row), 1, 0)

Keep in mind that it will look for each individual item within the range, versus the unique combination of 2 or three values, so if that value appears in {ProjectGroup} anywhere at all you'll receive a 1. Is that what you're looking to do, or do you want to confirm unique combinations?

For unique combinations, I would use a COUNTIF statement to COUNT if this cell appears in the other range. If it does, then return 1. If this cell/combination doesn't appear, return 0:

=IF(COUNTIF({ProjectGroup}, [Group Name]@row) >= 1, 1, 0)

Let me know if this works for you!

Cheers,

Genevieve