Lookup date between two dates and return value

Caitlin Gaines

"Schedule" Helper Sheet: This sheet has the schedule of each sprint

Columns: Sprint, Start Date, Week End Date

Example Data Set:

Sprint 1 | 3/14/2022 | 3/18/2022

Sprint 1 | 3/21/2022 | 3/25/2022

Sprint 2 | 3/28/2022 | 4/1/2022

Sprint 2 | 4/4/2022 | 4/8/2022

Objectives/Milestones "Roadmap" Sheet:

Columns: Objective/Milestone, Set Start Date, Set End Date, Sprint Due

Example Data Set:

Obj - Milestone 1 | 3/14/2022 | 3/16/2022 | {formula should return Sprint 1}

Obj - Milestone 2 | 4/6/2022 | 4/6/2022 | {formula should return Sprint 2}

In the sprint due column, I need a formula that returns the sprint from "Schedule" based on which sprint the 'set end date' the obj-milestone falls within from the "Roadmap".

I've used a formula with INDEX, COLLECT, but it's only returning and exact match where the set end date is explicitly listed on the schedule helper sheet.

Julio S.

Hi @Caitlin Gaines,

If your formula returns the desired Sprint only when there is an exact match, that means that the operator used only looks for exact matches (=). I have recreated your environment and the following formula should return the first Sprint listed that has a date = or over the End Date in the relevant row:

=INDEX(COLLECT({Sprint}, {Week End Date}, >=[Set End Date]@row), 1)

I'm also attaching captures of each cross-sheet reference for your own reference

 

I hope this can be of help.

Cheers!

Julio

TLH

A little crude, but works:

First, create a folder to contain your project plan and a newly-added second page.

• Create a new second page. I called mine "Sprint & Holiday Schedule".
• In this second page, add three columns (from left to right): Sprint Date, Sprint, Holidays.
• NOTE: Holidays column is optional.
• Mine looks like this:

Second, in your main project plan:

• Add 2 columns: Sprint Start, Sprint End.
• In Sprint Start cell, use VLOOKUP: =VLOOKUP([Latest Start]@row, {Copy of Sprint & Holiday Schedule Range 1}, 2, true). Apply formula to Sprint End.
• "2" = column 2 containing sprint number.
• Mine looks like this: