Detecting where the sequence changes

Originally Posted by 63falcondude

Try this:

=INDEX(B2:KO2,MATCH(TRUE,A3:KN3<>B3:KO3,0)) Ctrl Shift Enter

or the non-CSE alternative:

=INDEX(B2:KO2,INDEX(MATCH(TRUE,A3:KN3<>B3:KO3,0),0))

Originally Posted by Richard Buttrey

...how about n array formula

Formula:

=INDEX(B2:KO2,1,MATCH(TRUE,A3:KO3<>B3:KP3,FALSE))

Damn, Falcondude beat me to it!

Originally Posted by Pete_UK

You have a sequence of 85 threes starting with cell A3, and no other cells after that which contain 3, so in that case you can use this formula in cell KQ3:

=INDEX(2:2,COUNTIF(A3:KO3,A3)+1)

If you do have any other cells with 3 in them after the first sequence, then this formula will produce erroneous results.

Hope this helps.

Pete

Top stuff all many many thanks