Trouble with COLLECT in INDEX/MATCH

James Vandezande

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!

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(Function@row = "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")

James Vandezande

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.

Cheryl Hishmeh

Can I do something similar to find a match that fits in a date range

Paul Newcome

@Cheryl Hishmeh Are you able to provide more detail?