Trouble with COLLECT in INDEX/MATCH

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)

Formula:

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

Thanks in advance for any assistance!

collect-schedule.png

collect-result.png

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!