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

Grace N

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

Andrée Starå

Hi Grace,

Did you get it working?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

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.

.

EDIT

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

 

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)))