If statement with VLookup

I am having trouble setting up a function that joins my two sheets on multiple criteria.

I want the following to happen in the flight column:

IF sheet1.product = sheet2.product AND
sheet1.date >= sheet2.start date AND
sheet1.date <= sheet2.end date THEN
sheet2.flight

I cannot concatenate and vlookup off that because I am looking for a range of dates and I cannot use if(and( because I need my 'value_if_true' to be dynamic.

What is the best formula to achieve what I have tried to explain?

Sheet1
For flight column row 1
=IF(AND(D2=Sheet2!A2,Sheet1!A2>=Sheet2!B2,Sheet1!A2<=Sheet2!C2),Sheet2!D2)

Date    Start Date  End Date    product      flight
11/29/2015  11/29/2015  12/5/2015   product1    1
11/29/2015  11/29/2015  12/5/2015   product1    
11/30/2015  11/29/2015  12/5/2015   product1    
11/30/2015  11/29/2015  12/5/2015   product1    
12/1/2015   11/29/2015  12/5/2015   product1    
12/1/2015   11/29/2015  12/5/2015   product1    
12/2/2015   11/29/2015  12/5/2015   product1    
12/3/2015   11/29/2015  12/5/2015   product1    
12/3/2015   11/29/2015  12/5/2015   product1    
12/4/2015   11/29/2015  12/5/2015   product1    
12/5/2015   11/29/2015  12/5/2015   product1    
11/25/2015  11/29/2015  12/5/2015   product2    
11/26/2015  11/29/2015  12/5/2015   product2    
11/27/2015  11/29/2015  12/5/2015   product2    
11/29/2015  11/29/2015  12/5/2015   product2    
11/29/2015  11/29/2015  12/5/2015   product2    
11/30/2015  11/29/2015  12/5/2015   product2    
11/30/2015  11/29/2015  12/5/2015   product2    
12/1/2015   11/29/2015  12/5/2015   product2    
12/1/2015   11/29/2015  12/5/2015   product2    
12/2/2015   11/29/2015  12/5/2015   product2    
12/2/2015   11/29/2015  12/5/2015   product2    
12/3/2015   11/29/2015  12/5/2015   product2    
12/3/2015   11/29/2015  12/5/2015   product2    
12/4/2015   11/29/2015  12/5/2015   product2    
12/4/2015   11/29/2015  12/5/2015   product2    
12/5/2015   11/29/2015  12/5/2015   product2    
12/6/2015   11/29/2015  12/5/2015   product2    

Sheet2
product   start date    end date    flight
product1    11/29/2015  12/1/2015   1
product1    12/2/2015   12/5/2015   2
product2    11/25/2015  11/30/2015  1
product2    12/1/2015   12/2/2015   2
product2    12/3/2015   12/6/2015   3

Any help much appreciated. thanks.