Why is my IF(AND(HAS function only checking part of the AND statement?

SadieHahn

I am using a checkbox column to return 1 if a row on this sheet matches the Student Name and Location Name on a row of another sheet.

Formula: =IF(AND(HAS({Extern}, [Student Name]@row), HAS({Location_No_ID}, [Assigned Clinic]@row)), 1, 0)

The result is showing a checkbox when only part of the statement is true. I need both statements to be true in order to trigger the checkmark.

Any help is GREATLY appreciated.

Carson Penticuff

As written, your formula is checking whether there is a match for [Student Name]@row anywhere in the {Extern} column, and [Assigned Clinic]@row anywhere in the {Location_No_ID} column, regardless of whether the two matches are in the same row or not. If I understand your request correctly, this should give you the desired result:

=IF(COUNTIFS({Extern}, HAS(@cell, [Student Name]@row), {Location_No_ID}, HAS(@cell, [Assigned Clinic]@row)) > 0, 1, 0)

Carson Penticuff

As written, your formula is checking whether there is a match for [Student Name]@row anywhere in the {Extern} column, and [Assigned Clinic]@row anywhere in the {Location_No_ID} column, regardless of whether the two matches are in the same row or not. If I understand your request correctly, this should give you the desired result:

=IF(COUNTIFS({Extern}, HAS(@cell, [Student Name]@row), {Location_No_ID}, HAS(@cell, [Assigned Clinic]@row)) > 0, 1, 0)