Find date within a range of dates and input a value in the next free cell

[usurrao]

I have a list of dates in sheet 1 and a second sheet with a range of dates. I need to compare the first list of dates and have the "period" value input when the date falls in the range. I used the sumproduct and index/match but get a "0" not the value in the relevant cell.

I have attached a file which has the data and the formula I used which did not work.

[NBVC]

Sumproduct can only return numeric outputs...

Try:

=LOOKUP(2,1/((PeriodEndTable!$B$2:$B$13<=A2)*(PeriodEndTable!$C$2:$C$13>=A2)),PeriodEndTable!$A$2:$A$13)

copied down.