Using #INDEX & #MATCH cross sheet reference formula help

Natalia Kataoka

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

Paul Newcome

Ok. Here is a little tweak...

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

Paul Newcome

Are all codes unique?

Natalia Kataoka

Hi Paul,

Yes all codes are unique.

Paul 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(Code@row, {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(Code@row, {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(Code@row, {Sheet 2 Code Column}, 0)) = "Approved - Job Specific"), 1, 0)

Natalia 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(Code@row, {Under Insured Co | code}, 0)) = "Approved - job specific"), 1, 0)

Paul Newcome

Ok. Here is a little tweak...

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

Natalia Kataoka

That worked, thank you!

Paul Newcome

Happy to help. 👍️