# Thread: Check a list of dates to see if any fall between two dates, return index within list

1. ## Check a list of dates to see if any fall between two dates, return index within list

I have a list of holidays for a given year (E10-E16). I also have a table which represents the sequential weeks of the year; Weeks 1-52 (B2-M3 is a portion of this table). This table has a column for each week of the year. The first two rows of the table contain the date of the Monday and the Friday in that week e.g. 1/2/2017 and 1/6/2017 respectively for the 1st week of 2017. What I want to achieve is.... for each week, given the "start" and "end" dates for that week, look through the list of holidays and see if any holiday falls between the "start" and "end" dates for that week. If so, I want to know the INDEX of the date within the list of holidays that falls between those two dates. In the example below, I would have a formula in cells B4-M4 that would use this data to indicate whether or not a holiday falls within each week. e.g. D4 and K4 indicate holidays. Thanks in advance for your help!

2. Hi and welcome
although very nice to look at an image cannot be worked with. Please post a sample sheet showing data and expected results ( Click Go advanced - Manage Attachments).

Thank you

3. ## Return specific date if match between dates

Originally Posted by Gster
Well said @pecoflyer, but I have time for you
On Sheet2 put a list of holidays
In cell B4 try next ARRAY formula below (copy the right)
ARRAY or CSE formula should be finished by pressing Ctrl+Shift+Enter (not just enter)

Code:
`=IF(AND(IF(COUNTIFS(Sheet2!\$B\$2:\$B\$20;">="&B2;Sheet2!\$B\$2:\$B\$20;"<="&B3;Sheet2!\$B\$2:\$B\$20;"<>")>0;INDEX(Sheet2!\$B\$2:\$B\$20;IF(ISNA(MATCH(B\$2;Sheet2!\$B\$2:\$B\$20));1;MATCH(B\$2;Sheet2!\$B\$2:\$B\$20)+ISNA(MATCH(B\$2;Sheet2!\$B\$2:\$B\$20;0))));"")>=B2;IF(COUNTIFS(Sheet2!\$B\$2:\$B\$20;">="&B2;Sheet2!\$B\$2:\$B\$20;"<="&B3;Sheet2!\$B\$2:\$B\$20;"<>")>0;INDEX(Sheet2!\$B\$2:\$B\$20;IF(ISNA(MATCH(B\$2;Sheet2!\$B\$2:\$B\$20));1;MATCH(B\$2;Sheet2!\$B\$2:\$B\$20)+ISNA(MATCH(B\$2;Sheet2!\$B\$2:\$B\$20;0))));"")<=B3);IF(COUNTIFS(Sheet2!\$B\$2:\$B\$20;">="&B2;Sheet2!\$B\$2:\$B\$20;"<="&B3;Sheet2!\$B\$2:\$B\$20;"<>")>0;INDEX(Sheet2!\$B\$2:\$B\$20;IF(ISNA(MATCH(B\$2;Sheet2!\$B\$2:\$B\$20));1;MATCH(B\$2;Sheet2!\$B\$2:\$B\$20)+ISNA(MATCH(B\$2;Sheet2!\$B\$2:\$B\$20;0))));"");"")`
btw: I believe that next time you will attach the Excel file instead of picture

4. My apologies PecoFlyer. That's why I'm a Neophyte, right?

I have attached a sample spreadsheet.

Navic, that formula seems to work. Thanks so much! I need to learn more about array formulas.

5. you are welcome

6. Hi navic,

What if I wanted to know the INDEX of the date within the list of holidays? See attached spreadsheet...yes, I remembered!

7. Originally Posted by Gster
What if I wanted to know the INDEX of the date within the list of holidays?
try
Code:
`=VLOOKUP(D6;Sheet2!F5:G13;2;FALSE)`

8. What do you need this "Index"' for?

9. See the revised spreadsheet with the example.

10. ## VLOOKUP function in Excel

Originally Posted by Gster
See the revised spreadsheet with the example.
Try in B5 (copy across)
Code:
`=IFERROR(VLOOKUP(B6;Sheet2!\$F\$5:\$I\$17;4;FALSE);"")`
see attachment

Page 1 of 2 1 2 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•