Return task name associated with a future date

Grace N
Grace N
edited 12/09/19 in Formulas and Functions

I have a project with linked tasks. I can get the start date of the next task using the following formula:

=MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY()))

How can I return the task name associated with this upcoming task?

In this example, this formula gives the next start date as 6/19/19. What formula will return ‘Task 3’?

Thanks. I’ve learned a lot from this community

20190605 Test timeline.png


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could use an INDEX/MATCH formula, but it will only return the first task name if there are multiples. It would look something like this...


    =INDEX([Task Name]2:[Task Name]6, MATCH(Start2:Start6, MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY())), 0))


    If you wanted to join all of the tasks together if there are multiples, you could use a JOIN/COLLECT formula like this...


    =JOIN(COLLECT([Task Name]2:Task Name]6, Start2:Start6, MIN(COLLECT(Start2:Start6, Start2:Start6, >TODAY()))), ", ")

  • Grace N
    Grace N
    edited 06/07/19

    Thanks but when I pasted your formula into my sheet, I get an INCORRECT ARGUMENT SET message. 

    20190606a Test timeline 1.png

    20190606 Test timeline 1.png

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Grace,

    Did you get it working?

    Have a fantastic weekend!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/12/19

    Sorry for the delay. I just now saw this. It looks like it's a syntax issue within your MATCH.


    You have


    MATCH(range, what to look for.....


    It should be


    MATCH(what to look for, range......


    Switch your MIN(COLLECT( to the first portion of the MATCH statement, and that should clear it up for you.



    I just realized you entered the formula exactly as I had specified, so that's on me. My apologies.


  • Tori Green
    Tori Green ✭✭✭

    I'm also having difficulty with the Syntax, trying to get the data in the Deadline cell to populate into a summary for the next upcoming deadline. Here is my formula: =INDEX(Deadline1:Deadline16, MATCH([Due Date]1:[Due Date]16, MIN(COLLECT([Due Date]1:[Due Date]16, [Due Date]1:[Due Date]16 > TODAY()), 0)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!