Lookup date between two dates and return value

"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.
    Julio S. Moderator
    edited 03/16/22

    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.



  • TLH
    TLH ✭✭
    edited 10/17/23

    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:

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!