Find That Date
 

Hi there,
Can you please help me to slove this problem.

I have two spreadsheets the first with a list of dates and other info.
And the second with a table like the one below.

Year/Period Month Start Period End Period
2004-01 April-2004 01-Apr-04 23-Apr-04
2004-02 May-2004 24-Apr-04 21-May-04
2004-03 June-2004 22-May-04 25-Jun-04
2004-04 July-2004 26-Jun-04 23-Jul-04

I want to look up the date in the first spreadseet to see if it falls
between the start and end period and bring me back the year/period.
Notice that the periods may have two months in it.

I cannot use a nested if statement because I have a number of years to
consider. I could use a maco to test each line but wanted to know if
there is a formular that I can use insead.

Thanks in advance
Denz

Find That Date
 

Assuming that the periods are sorted and contiguous, I would use

=INDEX(period_range,MATCH(date,start_range))

Since you posted to the programming newsgroup, you may want to do this
from VBA; use Application.INDEX, Application.MATCH, and range objects.

Jerry

Denz wrote:

Hi there,
Can you please help me to slove this problem.

I have two spreadsheets the first with a list of dates and other info.
And the second with a table like the one below.

Year/Period Month Start Period End Period
2004-01 April-2004 01-Apr-04 23-Apr-04
2004-02 May-2004 24-Apr-04 21-May-04
2004-03 June-2004 22-May-04 25-Jun-04
2004-04 July-2004 26-Jun-04 23-Jul-04

I want to look up the date in the first spreadseet to see if it falls
between the start and end period and bring me back the year/period.
Notice that the periods may have two months in it.

I cannot use a nested if statement because I have a number of years to
consider. I could use a maco to test each line but wanted to know if
there is a formular that I can use insead.

Thanks in advance
Denz