How to:Find the row of the first cell not equal to several values
 

I have a column with values "OB" followed by several values "IB" and I wat to
see if there is a value different from these and return its row.

that's my array function "={MIN(IF(AND(C3:C500<"OB",
C3:C500<"IB"),ROW(C3:C500),12345678)}" but it's not working correctly

However, when I want to find the first value different from "OB"(in my case
thats the row of the first "IB" value) I use the function:

"{=MIN(IF(C3:C500<"OB",ROW(C3:C500), 12345678)}" and it's working correctly.

Can anybody tell me what's wrong with the first formula and what do I have
to change in it?

How to:Find the row of the first cell not equal to several values
 

Hi,

In Array formulas, AND is replaced by sign * , OR is replaced by
sign + ...

HTH
Cheers
Carim

How to:Find the row of the first cell not equal to several values
 

Try this

=MIN(IF((C3:C500<"OB")*(C3:C500<"IB"),C3:C500,12 345678))

ctrl shift enter (not just enter)

"Vasil Ivanov" wrote:

I have a column with values "OB" followed by several values "IB" and I wat to
see if there is a value different from these and return its row.

that's my array function "={MIN(IF(AND(C3:C500<"OB",
C3:C500<"IB"),ROW(C3:C500),12345678)}" but it's not working correctly

However, when I want to find the first value different from "OB"(in my case
thats the row of the first "IB" value) I use the function:

"{=MIN(IF(C3:C500<"OB",ROW(C3:C500), 12345678)}" and it's working correctly.

Can anybody tell me what's wrong with the first formula and what do I have
to change in it?

How to:Find the row of the first cell not equal to several values
 

Hi Vasil,

What about this non-array formula:

=IF(COUNTIF(C3:C20,"OB")+COUNTIF(C3:C20,"IB")=COUN TA(C3:C20),"no 3rd
value","3rd value present!")

Adjust Range!

Regards,
Stefi


€žVasil Ivanov€� ezt Ã*rta:

I have a column with values "OB" followed by several values "IB" and I wat to
see if there is a value different from these and return its row.

that's my array function "={MIN(IF(AND(C3:C500<"OB",
C3:C500<"IB"),ROW(C3:C500),12345678)}" but it's not working correctly

However, when I want to find the first value different from "OB"(in my case
thats the row of the first "IB" value) I use the function:

"{=MIN(IF(C3:C500<"OB",ROW(C3:C500), 12345678)}" and it's working correctly.

Can anybody tell me what's wrong with the first formula and what do I have
to change in it?