Lookup with multiple conditions

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

--
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

"linglc" <linglc@discussions.microsoft.com> wrote in message
news:21F762F7-11AB-4404-A703-4F55924DC1EF@microsoft.com...
>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
>
> --
> 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

"linglc" wrote:

> 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
>
> --
> help needed

thanks a mil! it works.
--
help needed


"Marcus Langell" wrote:

> In B1, enter =OFFSET(C3,MATCH(A1,A4:A13),0)
> (Based on that you have the cell containing "From" at adress A3)
>
> /Marcus
>
> "linglc" wrote:
>
> > 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
> >
> > --
> > help needed

this formula works as well
--
help needed


"N Harkawat" wrote:

> =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
>
> "linglc" <linglc@discussions.microsoft.com> wrote in message
> news:21F762F7-11AB-4404-A703-4F55924DC1EF@microsoft.com...
> >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
> >
> > --
> > help needed
>
>
>