Formula to check a box if it meets multiple criteria in another sheet

Hi!

I have two sheets:

Sheet A: ID#, Date, and a checkbox

Sheet B: ID#, Date

Can someone please tell me what should the formula be if I want to Sheet A checkbox to check if Sheet A's ID# and Date match with Sheet B's ID# and Date.

I tried a few different formula and it seems like it doesn't work:

=IF(AND(MATCH([ID #]@row, {Health Care Survey ID #}), MATCH(Date@row, {Health Care Survey Date}, 0)), 1)

=IF(INDEX(COLLECT({Health Care Survey ID #}, {Health Care Survey People #}, [ID #]@row, {Health Care Survey Date}, Date@row), 1), 1)

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Kelly Moore

Hey @Christina Lam

Try this

=IF(Date@row = INDEX({Health Care Survey Date},MATCH([ID #]@row, {Health Care Survey ID #},0)),1)

Does it work for you?

All comments

Kelly Moore

Hey @Christina Lam

Try this

=IF(Date@row = INDEX({Health Care Survey Date},MATCH([ID #]@row, {Health Care Survey ID #},0)),1)

Does it work for you?

Christina09

Hi @KDM ,

Thank you so much for replying me. You saved my life!!

This works perfectly!!

Kelly Moore

So glad I could help. Have a great weekend

cheers

Kelly

Christina09

@Kelly Moore

I might need your help again. The formula works if there isn't the same ID# for different dates.

For example:

ID#1111 Date 8/10

ID#2222 Date 8/10

ID#3333 Date 8/10

ID#1111 Date 8/11

ID#2222 Date 8/11

The check box will be check for ID#1111 Date 8/11, ID#2222 Date 8/11, and ID#3333 8/10 since there isn't a duplicate ID# even though the date is different.

Hope this makes sense.

Thanks.

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.