James Vandezande
James Vandezande ✭✭✭
edited 12/09/19 in Formulas and Functions

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)


=IFERROR(INDEX({Task}, MATCH(MIN(COLLECT({Start}, {Function}, ="Deliverables", {Start}, >TODAY())), {Start}, 0)), "NONE DEFINED")

Thanks in advance for any assistance!




  • Paul Newcome
    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")

  • 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
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes


    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
    Paul Newcome ✭✭✭✭✭✭

    @Cheryl Hishmeh Are you able to provide more detail?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!