Can I use Vlookup to check a date range!

Dietmar Schmitz

Hi All,

I have a multiple scrum board sheets. In these sheets I have a closed date column. I also have a field with the Sprint # an Quarter. This sprint number field is not relevant to the task owners, it is more to internally map the tasks and do some statisctics.

I have another sheet (my refernce sheet) which gives a sprint #, a start date, an end date and a fiscal year quarter. Between start date and end date there is a 14 day period. For each fiscal year I have a new reference sheet: Example:

Sprint Start Date End Date Sprint# Quarter

Sprint 01 01.01.2021 14.01.2021 1 FY21-Q1

Sprint 02 15.01.2021 28.01.2021 2 FY21-Q1

....

My wish is that I can use the closed date to automatically deliver e.g. Sprint # and Quarter:

Task assignet to Closed Date Sprint# Quarter

Task.. me 19.01.2021 2 FY21-Q1

Any toughts on that?

Genevieve P.

Hi @Dietmar S.

You can use a cross-sheet INDEX(COLLECT to look for multiple criteria and return content based on that.

It works like this:

=INDEX(COLLECT({range to pull from}, {first criteria range}, first criteria, {second criteria range}, second criteria), 1)

So for your example, try something like this for the Sprint# column in the destination sheet:

=INDEX(COLLECT({Sprint Number Column}, {Start Date Column}, <=[End Date]@row, {End Date Column}, >=[End Date]@row), 1)

This looks to see if the End Date in this row is in the future from the Start Date in the other sheet, and also if the End Date in this row is in the past compared to the End Date in the other sheet.

Then to find the Quarter, just change out the range at the very beginning of the formula:

=INDEX(COLLECT({Quarter Column}, {Start Date Column}, <=[End Date]@row, {End Date Column}, >=[End Date]@row), 1)

Let me know if this works for you!

Cheers,

Genevieve