Using #INDEX & #MATCH cross sheet reference formula help


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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!