cell reference within a list

  • Thread starter Thread starter Jamie Metcalfe
  • Start date Start date
J

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
 
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
 
Back
Top