使用#指数& #匹配交叉表引用ence 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
-
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([email protected], {Under Insured Co | code}, 0)), "")= "Approved - job specific"), 1, 0)
Answers
-
Paul Newcome ✭✭✭✭✭✭
-
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([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)
-
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([email protected], {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([email protected], {Under Insured Co | code}, 0)), "")= "Approved - job specific"), 1, 0)
-
Natalia Kataoka ✭✭✭✭✭
That worked, thank you!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Check out theFormula Handbook template!
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/70973/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":36,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"
