Matching Id's, if id matches check if a certain date is between a beginning and ending date

Question

I have a quistion on how to check if a certain date falls between two dates after i found the matching id. If thats not true I want to check the next matching id's dates and so on.

I have tried with an INDEX(MATCH()) function and searched the forum but can't figure this one out. No problem if it's a formula or VBA code.

Here is an example of the tables I want to compare.

 Sheet 1
 Id    Date
 1     05-06-2017
 1     06-11-2017
 1     09-25-2017
 2     05-12-2017
 2     06-14-2017
 3     09-30-2017
 4     01-06-2017       
 4     05-20-2017  

 Sheet 2
 Id    Start Date        End date
 1     01-01-2017        02-01-2017
 1     03-15-2017        03-20-2017
 1     09-05-2017        09-28-2017
 2     05-10-2017        05-11-2017
 2     05-12-2017        05-20-2017
 3     09-01-2017        10-02-2017
 4     01-01-2017        01-07-2017
 5     05-01-2017        05-25-2017

I want to check if the id's from sheet 1 exist in sheet 2. After that I want to check if the date of the id in sheet 1 is between the start and end date matching the id in sheet 2. The problem im haveing is that i cant get excel to check if the next matching id is between the start and end date if the first one is'nt true and so on.

What i tried first was: =INDEX(Sheet2 A:A;MATCH(Sheet1A1;Sheet2A:A;1;0)) which gives me the id's.

Then i tried:

=IF(INDEX(Sheet2 A:A;MATCH(Sheet1A1;Sheet2A:A;1;0))=A1;IF(AND(B1>=Startdate;B‌​1<=Enddate);"True";"‌​False");"")

Answer 1

Try this...

On Sheet1

In C2

=AND(ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$9,0)),SUMPRODUCT((Sheet2!$A$2:$A$9=A2)*(B2>=Sheet2!$B$2:$B$9)*(B2<=Sheet2!$C$2:$C$9))>0)

and then copy it down.

Answer 2

use this formula:

=IF(ISNUMBER(MATCH(1,INDEX((Sheet2!$A$2:$A$9=A2)*(Sheet2!$B$2:$B$9<=B2)*(Sheet2!$C$2:$C$9>=B2),),0)),"Exists","Does not Exist")