Trouble with COLLECT in INDEX/MATCH
I'm trying to troubleshoot an INDEX/MATCH formula that contains a COLLECT statement which is supposed to limit what cells are being used for the MATCH.
I am referencing a project schedule, looking for the "next deliverable" - we have a column called "Function" in which the schedule tasks can be defined as such. (See screenshot)
As you can see in the sample data, there are two items with the same Start Date, but only one is defined as a "Deliverable" - which the criterion in the COLLECT formula should account for...but it seems to be failing to filter the results, showing the cell above the one that should be shown. (see second screenshot)
Formula:
=IFERROR(INDEX({Task}, MATCH(MIN(COLLECT({Start}, {Function}, ="Deliverables", {Start}, >TODAY())), {Start}, 0)), "NONE DEFINED")
Thanks in advance for any assistance!
Comments
-
Paul Newcome ✭✭✭✭✭✭The problem is that the COLLECT is only pulling a date. You are then using that date in the MATCH function. The COLLECT is not actually limiting the MATCH range.
I would suggest adding a helper date type column on the source sheet. You can then use the formula of
=IF([email protected]= "Deliverables", [Start Date]@row)
.
You could then use this for your INDEX/MATCH similar to...
=IFERROR(INDEX({Task}, MATCH(MIN(COLLECT({Helper}, {Helper}, >TODAY())), {Helper}, 0)), "NONE DEFINED")
-
Thanks Paul!
I continued to think about how my formula was functioning and I think I came to the same conclusion. I was able to solve it this way (without the need of a "helper" column)...
=IFERROR(INDEX(COLLECT({Schedule-Task}, {Schedule-Function}, "Deliverables"), MATCH(MIN(COLLECT({Schedule-StartDate}, {Schedule-Function}, "Deliverables", {Schedule-StartDate}, >TODAY())), COLLECT({Schedule-StartDate}, {Schedule-Function}, "Deliverables"))), "NONE DEFINED")
Instead of indexing the ENTIRE Task column in my schedule, I added COLLECT after the first INDEX call, thus limiting what I'm indexing to just the cells where Function = Deliverables.
Then I'm able to do the MATCH/MIN function to find the next date after TODAY.
Thanks for taking a look!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help!
I'm glad you were able to find a working solution. As you can see... There are often numerous ways to get what you need when it comes to formulas.
-
Can I do something similar to find a match that fits in a date range
-
Paul Newcome ✭✭✭✭✭✭
@Cheryl HishmehAre you able to provide more detail?
Help Article Resources
Categories
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/60161/\")<\/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":"
