Get the second Occurence of something

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

in J:J i have the Codes (AA.......XX)
in K:K i have the starting date of the interval =left(A2,10) and
in L:L i have the end dates = right(A2,10) 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 ideas on how to approach it in an other way?
Thanks .

 

[Bernie Deitrick]

Totti,

=INDEX(OFFSET($J$3:$J$15,MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L$15)),0),0),MATCH(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))

Array entered, of course.

Though this part (which appears three times)

MATCH(1,((A2>$K$3:$K$15)*(A2<$L$3:$L$15))

could go into its own cell and be referenced.

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

This will find the *last* instance. Note that if there is only one instance
that will be both the first and the last instance!

=LOOKUP(2,1/((A2>K3:K15)*(A2<L3:L15)),J3:J15)

 

[Totti]

Thank you both,
Bernie this seems to do its job great, Cheers