find 2nd occurence

[Totti]

Hi all,
I have a row of dates like this:
10.03.2008
10.26.2008
11.16.2008
11.17.2008
12.16.2008
01.08.2009
......
on the same sheet there is a table with dates intervals like this one:
Time Interval Code
01.07.2008 - 03.19.2008 A
03.06.2008 - 03.18.2008 B
03.14.2008 - 06.30.2008 C
05.24.2008 - 09.12.2008 D
09.13.2008 - 11.17.2008 E
....
I was trying to find the first occurence of the dates from A:A in the
table of time intervals and get the code and actually i did, with this
formula in B:B :
=INDEX($J$3:$J$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L$15)),0))
and it works great,
Now i am trying to expand the formula to find the second occurence
(put it in C:C) different than the first one found in (B:B) and i got
stuck, i just cant see it, i am thinking that something should be done
inside my MATCH so to speak "start from where you stopped in B:B", can
this be done?
or otherwise any ides on how to approach it in an other way?
Thanks .

 

[Ste]

Hi all,
I have a row of dates like this:
10.03.2008
10.26.2008
11.16.2008
11.17.2008
12.16.2008
01.08.2009
.....
on the same sheet there is a table with dates intervals like this one:
Time Interval                Code
01.07.2008 - 03.19.2008 A
03.06.2008 - 03.18.2008 B
03.14.2008 - 06.30.2008 C
05.24.2008 - 09.12.2008 D
09.13.2008 - 11.17.2008 E
...
I was trying to find the first occurence of the dates from A:A in the
table of time intervals and get the code and actually i did, with this
formula in B:B :
=INDEX($J$3:$J$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L$15)),0))
and it works great,
Now i am trying to expand the formula to find the second occurence
(put it in C:C) different than the first one found in (B:B) and i got
stuck, i just cant see it, i am thinking that something should be done
inside my MATCH so to speak "start from where you stopped in B:B", can
this be done?
or otherwise any ides on how to approach it in an other way?
Thanks .

I've done almost everything but still can't make it with the letter
code problems. In fact, the solution you suggest for the B:B, doesn't
work for me. Instead, I did it with IF but what if the time intervals
where 50 or even 100?

Can anyone help?

 

[Totti]

I forgot to say that in K:K i have the starting date of the interval
=left(A2,10) and in J:J i have the end dates = right(A2,10)
and it works fine so if the date > start date * date < end date, this
will give me "1" in the truth table and i am matching the 1, and
getting the code.

 

[Ste]

I forgot to say that in K:K i have the starting date of the interval
=left(A2,10) and in J:J i have the end dates = right(A2,10)
and it works fine so if the date > start date * date < end date, this
will give me "1" in the truth table and i am matching the 1, and
getting the code.

So, if in K:K you have the starting date and in J:J the ending date, I
suppose that in L:L you have the letter codes? I understood what you
are trying to do, but this simply doesn't work all the way down to A:A
dates. I keep getting N/A till the cell corresponding to the last
interval, and then I get a VALUE error.

The IF thing, worked fine, but I am still not happy with that. I want
a simple formula, and not a 4-line one.

 

[Totti]

Found:
=INDEX(OFFSET($J$3:$J$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L$15)),0),
0),MA­TCH(1,((A2>OFFSET($K$3:$K$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L
$15)),0),0­))*(A2<OFFSET($L$3:$L$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L
$3:$L$15)),0),0))),­0))

Ste, what do you think?

 

[Ste]

Found:
=INDEX(OFFSET($J$3:$J$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L$15)),0),
0),MA­TCH(1,((A2>OFFSET($K$3:$K$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L
$15)),0),0­))*(A2<OFFSET($L$3:$L$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L
$3:$L$15)),0),0))),­0))

Ste, what do you think?

After 3 hours of thinking, I tried to use something like the solution
you gave but with no results. Your solution seems to work, it has some
results. Nice thought!

 

[Herbert Seidenberg]

This formula can give you any nth occurrence:
=INDEX(Code,SMALL(IF((Fix R>=Start)*(Fix R<=End),rown),VALUE(BinA C)))
For an Excel 2007 spreadsheet example see:
http://www.savefile.com/files/1891070