Return Sprint based on finish date

Alethea Pollack

I have a project schedule where I am tracking activities. I would like to return a sprint number in column "Sprint" based on the finish date. Example: Sprint 1 is 6/23 - 7/6 and the task ends 6/29 so I would like the column to return a value of "Q1 Sprint 1" I have set up a separate sheet with the Sprints and start/finish dates but not sure if that is the best method. Any recommendations on how to build this formula?

Mark Cronk

Hi Alethea,

Can you attach a screen shot of your sheet and an explanation of how you determine the naming - "Q1 Sprint 1" based on dates?

Happy to help.

Mark

Alethea Pollack

Hi Mark,

Thank you for reaching out. I've attached two screenshots - one of my project schedule and one of the sprint schedule I created in a separate sheet. I would like the sprint to auto-populate in the project schedule based on the end date.

Mark Cronk

Hi Alethea,

Looks like you can do this with a VLOOKUP if you move your Sprint sheet Finish column to the far left. Or, duplicate the sprint column to the right of Finish.

I'm you project sheet use this formula in the Sprint column:

=VLOOKUP(Finish@row, {insert external range}, 2)

Your external range will be your Sprint sheet with Finish as the first left column and Sprint as the 2nd column.

VLOOKUP will find a match or the first number below the lookup value. Should work for you.

Help?

Mark