Picking out a Period, in relation to certain cells
 

I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the 'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??

Picking out a Period, in relation to certain cells
 

=INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period
Dates'!C2:C14=T$),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vikki1603" wrote in message
...
I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the
'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period
Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??

Picking out a Period, in relation to certain cells
 

Hi Vikki,

copy the A column (Period) to column D, (assuming you have A=Period, B=Start
and C=End), than use a Vlookup function as:

=vlookup(e2,b2:d20,3,1)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"vikki1603" escreveu:

I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the 'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??

Picking out a Period, in relation to certain cells
 

This formula doesnt seem to be working, its giving a #VALUE! error??

"Bob Phillips" wrote:

=INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period
Dates'!C2:C14=T$),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vikki1603" wrote in message
...
I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the
'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period
Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??

Picking out a Period, in relation to certain cells
 

Thanks Marcelo, but I cannot use a vlookup as it is someone elses sheet so I
cannot move the columns about. I also need to find whether the date is
earlier or later than in the two 'start' and 'end' columns, and I cannot do
that with a vlookup.

"Marcelo" wrote:

Hi Vikki,

copy the A column (Period) to column D, (assuming you have A=Period, B=Start
and C=End), than use a Vlookup function as:

=vlookup(e2,b2:d20,3,1)

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"vikki1603" escreveu:

I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the 'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??

Picking out a Period, in relation to certain cells
 

I beleive you need to enter Bob's formula as an Array, meaning you commit it
with the Ctrl-Shift-Enter keys, all at once.


"vikki1603" wrote:

This formula doesnt seem to be working, its giving a #VALUE! error??

"Bob Phillips" wrote:

=INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period
Dates'!C2:C14=T$),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vikki1603" wrote in message
...
I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the
'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period
Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??

Picking out a Period, in relation to certain cells
 

Wow, that is brilliant. Thankyou both...

"Duke Carey" wrote:

I beleive you need to enter Bob's formula as an Array, meaning you commit it
with the Ctrl-Shift-Enter keys, all at once.


"vikki1603" wrote:

This formula doesnt seem to be working, its giving a #VALUE! error??

"Bob Phillips" wrote:

=INDEX('Period Dates'!A2:A14,MATCH(1,('Period Dates'!B2:B14<=T$)*('Period
Dates'!C2:C14=T$),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vikki1603" wrote in message
...
I need a formula that will display which period a set of dates belongs to.

The info looks like this, located in a sheet named 'Period Dates'.

Period Start End
Period 1 01/04/2006 28/04/2006
Period 2 29/04/2006 26/05/2006
Period 3 27/05/2006 23/06/2006
Period 4 24/06/2006 21/07/2006
Period 5 22/07/2006 18/08/2006
Period 6 19/08/2006 15/09/2006
Period 7 16/09/2006 13/10/2006
Period 8 14/10/2006 10/11/2006
Period 9 11/11/2006 08/12/2006
Period 10 09/12/2006 05/01/2007
Period 11 06/01/2007 02/02/2007
Period 12 03/02/2007 02/03/2007
Period 13 03/03/2007 30/03/2007

In another sheet, I have column T called 'date in'. I need the formula to
pick out this date, find it in the info above and return the corresponding
period.

However, it is proving difficult as the date is located between the
'start'
and 'end' dates.
I have tried IF(T4<='Period Dates'!C9,"5",IF(T4<='Period
Dates'!C10,"Period
6").
I can only do up to 8 IF statements though, is there another option??