Check a Box when a match is made on two criteria's between two sheets

Can someone assist on how I can revise the following formula so that a check a box is checked when a match is made on two criteria's between two sheets, Contract ID the two date fields. Both the contract ID and date fields are real values (not calculated). However, the formula is returning blank in the 2nd sheet (snippet #2, row 1) as if there is not a match.

Essentially when the check box in sheet #1 is checked(Partner Signature Confirmed), and the "Contract ID" & "Payroll Invoicing Date" in Sheet 1, matches the "Contract ID" and the "Date Partner Supervisor Signature Requested" in sheet 2, the Partner Signature Confirmed box should be checked for all records that meet the criteria.

=IFERROR(INDEX({Partner Sig Confirmed_Metric Sheet}, MATCH([Contract ID]@row, {Contract ID_Metric Sheet}, 0), MATCH([Date Partner Supervisor Signature Requested]@row, {Payroll Invoicing Date_PartnerSupervisor Signoff}, 0)), "")

Find more posts tagged with

Accepted answers

All comments

Carson Penticuff

Is there anyway you can show screenshots or mock-ups of your sheets without showing anything confidential? I'm somewhat confused as you are getting the row number for your INDEX statement while getting the column number from another.

Sblackbu

Hi Carson. Thank you for your response. I revised the question and added screenshots.

Sblackbu

Hi Carson. Thank you for your response. I revised the question and added screenshots.

Carson Penticuff

I don't see a reference to the Partner Signature Confirmed column in the first sheet, so if you don't have that, you will need to add it. In this formula, I used {Partner Signature Confirmed_Metric Sheet} to stick with your naming convention. Take a look at this and see what you think... I "think" I understand what you are trying to do.

=IFERROR(IF(COUNTIFS({Contract ID_Metric Sheet}, [Contract ID]@row, {Partner Signature Confirmed_Metric Sheet}, 1, {Payroll Invoicing Date_PartnerSupervisor Signoff}, [Date Partner Supervisor Signature Requested]@row) > 0, 1, 0), "")

Sblackbu

Hi Carson, Thanks for the formula, but it is still returning a blank and not checking the box in the 2nd sheet (snippet #2, row 1). When you say " I don't see a reference to the Partner Signature Confirmed", this column is in both worksheets, and is the value (checked box) that should trigger the checked box in sheet 2 when a match exists.

In a previous formula that I used where I only matched to one criteria (Contract ID), the formula worked, and marked the check box in the 2nd sheet, "=INDEX({Partner Sig Confirmed_PartnerSignoff sheet}, MATCH([Contract ID]@row, {Contract ID_Metric Sheet}, 0))" .

However, this is not accurate for the Use Case I'm trying to solve for as some Contract ID's will have multiple dates associated to the ID, and I only want to check the box when both the Contract ID and the referenced date columns match, which is why I needed to add a 2nd match criteria.

Sblackbu

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.