Return task name associated with a future date

Grace N

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

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()))), ", ")