INDEX/MATCH with a HAS Function for Multi-Select Column

Emily McClary

I am trying to create an INDEX/MATCH where the INDEX is a Checkbox column and the MATCH is a multi-select column. I am not sure how to incorporate the HAS function for the multi-select column. I would like the High Priority flag marked if any/1 or more of the Sites Under Consideration are flagged a High Priority in the separate sheet.

I have gotten the below results with the below formula, but it's not flagging the High Priority? column, instead inputing in True or False. It also doesn't appear to be functioning properly for every row.

=IF(NOT(ISBLANK([Sites Under Consideration]@row)), JOIN(COLLECT({Affiliate Sites Range 2}, {Key ID}, HAS(@cell, [Sites Under Consideration]@row)))

Any assistance is appreciated!

Paul Newcome

I would use an IF/COUNTIFS combo. If the number of rows in the reference sheet that contain one of the entries in the dropdown column and have a flag flagged is greater than zero, then flag it here on the destination sheet.

=IF(COUNTIFS({Key ID}, HAS(@cell, [Sites Under Consideration]@row, {Affiliate Sites Range 2}, @cell = 1)> 0, 1)

Emily McClary

@Paul Newcome Thank you! I tried that and got #incorrect argument set. Any ideas?

Paul Newcome

Sorry about that. I missed a parenthesis.

=IF(COUNTIFS({Key ID}, HAS(@cell, [Sites Under Consideration]@row), {Affiliate Sites Range 2}, @cell = 1)> 0, 1)

Emily McClary

@Paul Newcome Thank you. It is still not working for thew rows with more than one Sites Under Consideration. The rows highlighted yellow should be flagged, as they contain a high priority site on the Affiliate Sites sheet.

I appreciate your help! Let me know if you think of any other solution.

Paul Newcome

Let's try flipping the HAS argument.

=IF(COUNTIFS({Key ID}, HAS([Sites Under Consideration]@row, @cell), {Affiliate Sites Range 2}, @cell = 1)> 0, 1)

Emily McClary

@Paul Newcome THANK YOU SO MUCH! That worked! You are a gem!

Paul Newcome

Happy to help. 👍️