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

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.

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    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)

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!