Finding the next value that is different in a column

[SheilaV]

I would like to have a formula that can tell me what the next value is in a column that is different than a known value. If I have a column that has some value "A" and then later in the column changes to "B" (which could happen anywhere in the column), I would like to have two boxes that can tell me the values "A" and "B". I know how to get the value for "A" since I can look at the first cell of that column but I don't know how to get it to look for "B". It can't be that hard but I am just not finding it.

Thanks,

[NBVC]

This will find the first entry in A1:A10 that contains something other than what is in A1

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>A1,0),0))

[SheilaV]

That works great! How would I get it to return a blank if it doesn't find a different value?

[NBVC]

Try:

=IF(ISNUMBER(MATCH(TRUE,INDEX(A1:A10<>A1,0),0)),INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>A1,0),0)),"")

[SheilaV]

I am getting a #Value return, is it because my column contains alphanumeric entries? Is there something similar to isnumber I could use?

[NBVC]

The ISNUMBER() function is not checking that you have numeric data. It is checking that the result of the MATCH() function it surrounds is numeric... so doesn't matter your data.

The only thing I can tell is that perhaps you have an error in the range of data somewhere? Even then it would skip it...

can you post a sample workbook, no confidential data.