Lookup with multiple conditions

[Guest]

I have a list of dates which i want to look up but with multiple conditions. Eg

In Sheet 1 and cell A1, I have 8-Jun-2005. In cell B1, I want to return the
value 17-Jun-2005 because the date falls between 1-Jun-2005 and 30-Jun-2005.
What is the formula for this?

From To Reference
1-Jan-2005 31-Jan-2005 16-Jan-2005
1-Feb-2005 25-Feb-2005 18-Feb-2005
1-Mar-2005 31-Mar-2005 12-Mar-2005
1-Apr-2005 30-Apr-2005 20-Apr-2005
1-May-2005 31-May-2005 11-May-2005
1-Jun-2005 30-Jun-2005 17-Jun-2005
1-Jul-2005 31-Jul-2005 13-Jul-2005
1-Aug-2005 31-Aug-2005 13-Aug-2005
1-Sep-2005 30-Sep-2005 11-Sep-2005
1-Oct-2005 31-Oct-2005 10-Oct-2005

 

[N Harkawat]

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(--Sheet2!$A$1:$A$10<=A1)*(--Sheet2!$B$1:$B$10>=A1),0))
array entered (ctrl+shift+enter)
assuming that the sourrce table is in sheet 2 between ranges A1:a10

 

[Guest]

In B1, enter =OFFSET(C3,MATCH(A1,A4:A13),0)
(Based on that you have the cell containing "From" at adress A3)

/Marcus

 

[Guest]

thanks a mil! it works.
--
help needed

In B1, enter =OFFSET(C3,MATCH(A1,A4:A13),0)
(Based on that you have the cell containing "From" at adress A3)

/Marcus

 

[Guest]

this formula works as well
--
help needed

=INDEX(Sheet2!$C$1:$C$10,MATCH(1,(--Sheet2!$A$1:$A$10<=A1)*(--Sheet2!$B$1:$B$10>=A1),0))
array entered (ctrl+shift+enter)
assuming that the sourrce table is in sheet 2 between ranges A1:a10