Finding the first cell in a range that does not match a value

[leepox]

Hi all, I want to find the first cell that does not match a certain value.

For example I have this data:

First non blank value	First non- matching value	Column Reference		Data 1	Data 2	Data 3	Data 4	Data 5
AA	AB	4		AA		AA	AB	AA

<tbody>
</tbody>

The first 3 columns are the results. The rest of the columns are data. I have the following equation in Cell A2:

=INDEX(D5:FC5, MATCH("*", D5:I5, 0))

This returns the first non-blank text data and I use this as the reference value. The equation I require is for cell B2 which is to find the first non AA value. To makes matters more complex, the BLANK cells do not count as a change. So I guess the 2 questions are:

1. Find the first cell that does not match the reference value, and
2. How do I ignore the blank cells to find the first non-match value.

 

[Ron Coderre]

With your sample data in A1:I5 (repeating your test row)

This formula returns the first non-blank value

A5: =INDEX(D5:FC5, MATCH("*", D5:I5, 0))

This formula returns the value of the first non-blank, non-matching cell

B5: =INDEX(D5:I5,MATCH(1,INDEX((D5:I5<>"")*(D5:I5<>A5),0),0))

And this formula returns the cell count between those two values

C5: =MATCH(B5,D5:I5,0)-MATCH(A5,D5:I5,0)+1

Is that something you can work with?

 

[leepox]

Hi Ron,

Wow. This is exactly what I needed! I would never have figured out to do it this way. I have been pulling my hair for the past few hours, so thank you ever so much for stopping me from getting completely bald!

Kind Regards

 

[Mike Szczesny]

Hi leepox,
I realize that I have arrived late to the "party". Can you use something like this? I have 3 formulas: first non-blank item, first non-blank non-matching item, and first exact item. Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E	F	G	H
1	a
2	x	xx	xxx	xxxx	xxxxx	xxxxxx	xxxxxxx	xxxxxxxx
3	A	w	c		a	x	d	a
4
5	x	xx	xxxxx
Sheet1

Cell Formulas
Range	Formula
A5	{=INDEX($A$2:$H$2,MATCH(TRUE,$B$3:$H$3<>"",0))}
B5	{=INDEX($A$2:$H$2,MATCH(TRUE,$A$3:$H$3<>$A$1,0))}
C5	{=INDEX($A$2:$H$2,MATCH(1,--EXACT($A$3:$H$3,$A$1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.