cell reference within a list

[Jamie Metcalfe]

I am trying to return the first and last time a specified criteri
appears in a list.

For example a list of 1000 lines of which 400 are the cirteria code o
LX, I want to find out where the first LX appears and the last so
A200 and A600 respectively.

I don't need the first and last occasions in the same calculation.

Thanks

Jami

 

[Peo Sjoblom]

for the first occurrence

=MIN(IF(A2:A1000="LX",ROW(A2:A1000)))

for the last

=MAX((A2:A1000="LX")*(ROW(A2:A1000)))

both formulas will return the row number counted from row number 1
and both formulas have to be entered with ctrl + shift & enter