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.

Find more posts tagged with

Formulas

Community Platform

Accepted answers

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)

All comments

Carson Penticuff

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.