# Using #INDEX & #MATCH cross sheet reference formula help

Options
✭✭✭✭✭

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

• ✭✭✭✭✭✭
Options

Are all codes unique?

• ✭✭✭✭✭
Options

Hi Paul,

Yes all codes are unique.

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭
Options

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)

• ✭✭✭✭✭
Options

That worked, thank you!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!