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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Hi,

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

HTH
Cheers
Carim
 
Try this

=MIN(IF((C3:C500<>"OB")*(C3:C500<>"IB"),C3:C500,12345678))

ctrl > shift > enter (not just enter)
 
Hi Vasil,

What about this non-array formula:

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

Adjust Range!

Regards,
Stefi


„Vasil Ivanov†ezt írta:
 
Back
Top