INDEX/MATCH or VLOOKUP for Date Range

sahilhq

Hi,

1) I have sheet "X" set up as shown in screenshot 1 below where I have got a start date and end date column and the week numbers associated for the date ranges.

2) I have got another sheet "Y" set up with empty week number column as shown in screenshot 2.

Please suggest me with a formula to enter in the Week number column of sheet Y such that if the start date in sheet Y falls in the data range already set out in sheet X then the corresponding Week number from the column in sheet X is automatically picked up? I guess an index/match or vlookup could do the job for me? Thanks for your help in advance!

Paul Newcome

You are going to want something along the lines of...

=INDEX({Other Sheet Week Number Column}, MATCH(MAX(COLLECT({Other Sheet Start Date Column}, {Other Sheet Start Date Column}, @cell <= [Start Date]@row)), {Other Sheet Start Date Column}, 0))

Paul Newcome

You are going to want something along the lines of...

=INDEX({Other Sheet Week Number Column}, MATCH(MAX(COLLECT({Other Sheet Start Date Column}, {Other Sheet Start Date Column}, @cell <= [Start Date]@row)), {Other Sheet Start Date Column}, 0))

sahilhq

@Paul Newcome The formula didn't quite work. I noticed that we didn't use any end date column reference. Could this be the reason why it didn't work?

Sorry I might have not explained myself propertly. Just to reiterate, I would want the week column to be populated based upon the date range from the other sheet. If the date range (start date and end date) falls under the week category then autopopulate the week number if that makes sense?

Paul Newcome

What do you mean by "it didn't work"? Are you getting an error or an incorrect result?

In your original post, you said that if the Start Date falls within a specific date range to pull that corresponding week number.

The way the above formula is set up is that we pull the highest start date from the source sheet that is less than or equal to the start date in the target sheet and use that for the MATCH within an INDEX/MATCH.

Using your original screenshots, your sample date is 06/07/20. The highest date on the source sheet that is less than or equal to that date is 04/07/20. The MAX/COLLECT pulls that date for the MATCH function to hit against which in turn automates the row number for the INDEX function.

sahilhq

@Paul Newcome Apologies, the formula you suggested worked absolutely fine now. When I was trying to do it yesterday, I think I did in a rush and ended up getting an error. Anyways, thanks for your help man!!

Paul Newcome

Happy to help! 👍️

Susan Hildebrant

I have a similar question and with a minor modification I was able to determine what fiscal year (like week number) a project end fell in based on fiscal year end date(like Start Date). Changed the MAX to MIN and it works perfectly!

Thanks 😀

Alethea Pollack

@Paul Newcome This formula was very helpful! I changed the reference to finish date and updated the MAX to MIN as mentioned in the other comment and was able to return sprint values based on my finish date.

Thank you!!!

mlandof

How would you tweak this formula so that you pulled the week # into sheet Y based on the date range that today's date falls into? I think the TODAY() function needs to be used right?

Paul Newcome

@mlandof Are you able to provide some screenshots for reference?

Beth Klineman

I tried this formula and it did not work for me -- I verified it was the exact formula and I am getting the column formula syntax isn't quite right

=INDEX({Project Weeks Week #}, MATCH(MAX(COLLECT({Project Weeks Start}, {Project Weeks Start}, @cell <= [Start Date]@row)), {Project Weeks Start}, 0))

Paul Newcome

@Beth Klineman Are you able to provide a screenshot of the formula actually in the sheet itself?

Beth Klineman

@Paul Newcome I found the mistake and it worked :) I originally posted a separate discussion on this but found this thread to answer my question.

Cberrymusic

This looks like what I am looking for but apparently I am not doing it correctly. I am trying to get a given invoice due date on sheet 1 that falls between two dates (column one & column two) on sheet 2 to return a given Pay Date listed in column three on sheet 2. They are all in date format. I need it to return that Pay Date on the same row of sheet one as the due date in sheet one. The sheet 2 data goes for ~2 years worth of two-week periods.

Sheet 2:

Sheet 1

I tried adapting the following formula;

=INDEX({Other Sheet Week Number Column}, MATCH(MAX(COLLECT({Other Sheet Start Date Column}, {Other Sheet Start Date Column}, @cell <= [Start Date]@row)), {Other Sheet Start Date Column}, 0))

Any guidance is much appreciated.

Kelly Moore

Hey @Cberrymusic

Try this:

=IFERROR(INDEX(COLLECT({Your First Sheet Pay Dates}, {Your First Sheet Due Date Start}, ISDATE(@cell), {Your First Sheet Due Date End}, ISDATE(@cell), {Your First Sheet Due Date Start}, MAX(@cell) <= [Due Date]@row, {Your First Sheet Due Date End}, MIN(@cell) >= [Due Date]@row), 1),"No Matching Dates")

I added the ISDATE criteria in case you had any non-date values in either cell. Remember since this is referencing another sheet you must physically build the references rather than just copying and pasting the above formula into your sheet. I also added an error message in case there are no matching dates. If you don't want this you can delete all the text between the two quotes marks. Deleting the text will make the cell show as blank instead of an error. If you want the error message to read as something different, change the text between the two quote marks.

Does this work for you?

Kelly

Cberrymusic

Hi @Kelly Moore

I actually got both of these to work finally. This one worked quick and I like that I can make it blank when there is no due date entered. It will only let me go as far as 7 rows of the data set and then it defaults to no match where I now have it reading blank. The other one I can use for a full year but then I have an error message going all the way down my page. I can definitely use this and then just move my dates up as I get ready for them. My guess is the number of cell references maybe? Although the data set only goes 90 cells, (30 rows).

Thank you so much for the help. I can adapt this to a number of areas.

Thank you,

Chris