excel - If statement with VLookup -

i having trouble setting function joins 2 sheets on multiple criteria.

i want following happen in flight column:

if sheet1.product = sheet2.product , sheet1.date >= sheet2.start date , sheet1.date <= sheet2.end date sheet2.flight 

i cannot concatenate , vlookup off because looking range of dates , cannot use if(and( because need 'value_if_true' dynamic.

what best formula achieve have tried explain?

sheet1 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 appreciated. thanks.

---

error when changing column reference

getting #n/a! value not available error when change column reference d o. cell references identical (dataval dropdown same source) data below illustrate. mentioned in comments, formula worked before made 1 change of column d o.

formula (using ctrl-shift-enter):

=index('flighting schedule'!$f$3:index('flighting schedule'!f:f,match(1e+99,'flighting schedule'!f:f)),match(1,if((a2>='flighting schedule'!$b$3:index('flighting schedule'!b:b,match(1e+99,'flighting schedule'!f:f)))(a2<='flighting schedule'!$c$3:index('flighting schedule'!c:c,match(1e+99,'flighting schedule'!f:f)))(o2='flighting schedule'!$a$3:index('flighting schedule'!a:a,match(1e+99,'flighting schedule'!f:f))),1,0),0))

this data on active sheet:

date    colb    colc    start date   end date   colf    colg    colh    coli        colj    colk    coll    colm    flight  product 11/29/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 1   1524    #ref!   producta 11/29/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 691 2046191 #ref!   producta 11/30/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 3   1906    #ref!   producta 11/30/2015  emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-11 1152    2923570 #ref!   producta 12/1/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 2   3   #ref!   producta 12/1/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 0   49  #ref!   producta 12/2/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 0   2   #ref!   producta 12/3/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 0   433 #ref!   producta 12/3/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 642 2002431 #ref!   producta 12/4/2015   emp loc 11/29/2015  12/5/2015   type    zzz dim dim 4850000     2015-12 2   24  #ref!   producta 

this data on 'flighting schedule'

product  start date  end date       budget      rate flight producta    11/29/2015  11/30/2015  $10,000.00  $5.00   1 producta    12/01/2015  12/03/2015  $10,000.00  $5.00   2 producta    12/04/2016  12/06/14    $10,000.00  $5.00   3 

what want array formula then:

=index(sheet2!$d$2:index(sheet2!d:d,match(1e+99,sheet2!d:d)),match(1,if((a2>=sheet2!$b$2:index(sheet2!b:b,match(1e+99,sheet2!d:d)))*(a2<=sheet2!$c$2:index(sheet2!c:c,match(1e+99,sheet2!d:d)))*(d2=sheet2!$a$2:index(sheet2!a:a,match(1e+99,sheet2!d:d))),1,0),0)) 

being array formula 1 must use ctrl-shift-enter when done editing formula instead of enter or tab. if done correctly excel put {} around formula.

array formulas calculations exponential want limit formula extents of data. index(sheet2!d:d,match(1e+99,sheet2!d:d)) doing. find last cell in column d has number , sets bottom reference automatically. table on sheet 2 grows or contracts reference, using calculations necessary.