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

Find more posts tagged with

Accepted answers

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)

All comments

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...

Natalia Kataoka

That worked, thank you!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.