Index-Match-Collect failure

Hello - I have a formula that I use to find the next upcoming task showing as "Not Started" and assigned to "Joe Smith." It has worked seamlessly for months and now all of a sudden is returning an incorrect value and I am totally stumped as to why. Here is the formula:

=INDEX([Task Name]:[Task Name], MATCH(MIN(COLLECT(Start:Start, [Assigned To]:[Assigned To], HAS(@cell , "Joe Smith"), Status:Status, "Not Started")), Start:Start, 0))

The value it is returning is from a row where the "Assigned To" is "Sally Jones" and "Status" is "Complete." Things I've tried:

— I deleted the row and created a new task with the same info

— I changed the Assigned To

— I saved a new copy of the sheet, thinking it was a caching issue

The only thing that fixes it is if I change the date from 10/20/25 to 10/21/25 and then it returns the correct task which also happens to be on 10/20/25. Any help would be much appreciated!

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

The issue is that you are only pulling in a date to compare and matching on that. Even though the date you are matching is being filtered, the index results are not.

Try an INDEX/COLLECT instead and get rid of the MATCH function entirely.

Sarah541

Thanks so much for the quick response Paul. I found a way to get it to work. Hopefully you don't see any issues with this. I had to split it into two columns to avoid getting #NESTED CRITERIA errors.

"Earliest Date" column to get earliest date meeting all criteria:

=MIN(COLLECT(Start:Start, [Assigned To]:[Assigned To], HAS(@cell , "Joe Smith"), Status:Status, "Not Started"))

Then a Task Name column to find the task that meets all the criteria and the earliest date:

=INDEX(COLLECT([Task Name]:[Task Name], [Assigned To]:[Assigned To], HAS(@cell , "Joe Smith"), Status:Status, "Not Started", Start:Start, [Earliest Date]1), 1)

Paul Newcome

That does happen sometimes, and in those cases, I also end up splitting it out. Glad you were able to get it sorted.

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.