Picking out a Period, in relation to certain cells

[vikki1603]

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

 

[Bob Phillips]

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

 

[Marcelo]

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:

 

[vikki1603]

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

 

[vikki1603]

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.

 

[Duke Carey]

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]

Wow, that is brilliant. Thankyou both...

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.