or Explore Discussions

# 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

• ✭✭✭✭✭

Are all codes unique?

thinkspi.com

• ✭✭✭✭✭

Hi Paul,

Yes all codes are unique.

• ✭✭✭✭✭

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)

thinkspi.com

• ✭✭✭✭✭

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)

• ✭✭✭✭✭

That worked, thank you!

• ✭✭✭✭✭

Happy to help. 👍️

thinkspi.com