Date Range Lookup

[engine44]

Hello !,

I would like to create a formula that performs a date LOOKUP (in daily format as in Column A of the Daily tab) against a range of dates (in weekly format found in Columns A and B) in the Weekly tab and then return the corresponding value from column F.

My formula in column J of the Daily tab is only half completed; as in I only managed to check if a date is found in the Weekly Range using SUMPRODUCTS function. I am unable to develop that part of the formula that pulls the corresponding value from column F ( from "Weekly Signal" ) and return the value in the Daily tab column J.

What I need to achieve is illustrated in Column L in "Daily Signal".

As an example, 10-Jan-07 (cell A4 of the Daily tab) falls in between the week starting 8-Jan-07 and ending 12-Jan-07 (cell A2 and B2 of Weekly tab) and the formula must pick up the corresponding value "Up" from cell F2 in the Weekly tab.

Any help will be much appreciated !

[boopathiraja]

May be this

=LOOKUP(A2,Weekly!$A$2:$A$4,Weekly!$F$2:$F$4)

[engine44]

Thank you very much ! it works

Attached updated file with the LOOKUP formula.