Using #INDEX & #MATCH cross sheet reference formula help

08/31/20
Accepted

I am attempting to write a formula that says: IF {in sheet# 1} COI Type = "Job" OR IF Agreement Type = "Project" OR IF a row matching the code in sheet #1 matches a code in sheet #2 AND Waiver Type = "Approved-Job Specific" THEN check Approval Required box.

The first part of my formula works: =IF(OR([COI Type]@row = "JOB", [Agreement Type]@row = "PROJECT"), 1, 0)

I am unsure how to add the reference to the other sheet. I was attempting an index + match formula, but do not know how to tell it to look for the specific Waiver Type in sheet #2.

Sheet #1:


Sheet #2


Best Answer

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Are all codes unique?

  • Natalia KataokaNatalia Kataoka ✭✭✭✭✭

    Hi Paul,

    Yes all codes are unique.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. So you want to INDEX from the {Sheet 2 Waiver Approval Column} based on a MATCH in the {Sheet 2 Code Column}.

    =INDEX({Sheet 2 Waiver Approval Column}, MATCH([email protected], {Sheet 2 Code Column}, 0))


    Then you want to say that IF that comes back as "Approved - Job Specific" then check the box.

    =IF(INDEX({Sheet 2 Waiver Approval Column}, MATCH([email protected], {Sheet 2 Code Column}, 0)) = "Approved - Job Specific", 1)


    But you already have your IF/OR for the other requirements, so we just take the "logical statement" part and drop it right into to OR function.

    =IF(OR([COI Type]@row = "JOB", [Agreement Type]@row = "PROJECT", INDEX({Sheet 2 Waiver Approval Column}, MATCH([email protected], {Sheet 2 Code Column}, 0)) = "Approved - Job Specific"), 1, 0)

  • Natalia KataokaNatalia Kataoka ✭✭✭✭✭

    This works great if there is a match on sheet #2, but I am getting a #NOMATCH error on any other row, whether they meet the other requirements or not.

    =IF(OR([COI Type]@row = "JOB", [Agreement Type]@row = "PROJECT", INDEX({Under Insured Co | Waiver type}, MATCH([email protected], {Under Insured Co | code}, 0)) = "Approved - job specific"), 1, 0)


  • Natalia KataokaNatalia Kataoka ✭✭✭✭✭

    That worked, thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.