How to find first row where condition is true?
 

Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly braces.

Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied
down to confirm that the OR function first returns TRUE in row 4.

How to find first row where condition is true?
 

In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2))
Copy down. Leave N1 empty.

(I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful)

Then you could use in say, M2: =MATCH(1,N:N,0)
to get the "first" row number which satisfies the conditions

Above helps in some way? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"WhatsUp31415" wrote:
Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly braces.

Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied
down to confirm that the OR function first returns TRUE in row 4.

How to find first row where condition is true?
 

Maybe this array formula** :

=INDEX(ROW(K:K),MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"WhatsUp31415" wrote in message
...
Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly braces.

Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied
down to confirm that the OR function first returns TRUE in row 4.

How to find first row where condition is true?
 

"Max" wrote:
I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful

Why? Rhetorical question. Only I can determine the "meaningfulness" of
conditions to test. In this case, K2:K4888 is fully populated, just as
columns H, I and J are. So the extra condition is irrelevant


In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2))
Copy down. Leave N1 empty.
[....]
Then you could use in say, M2: =MATCH(1,N:N,0)

Well, if I wanted to use helper cells, I would simply use
=OR(H2<K2,I2<K2,J2<K2), as I did in my original posting. Computationally
more efficient.

And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row
number, not simply the array index. I use ROW so that the formula is
updated automagically if I insert rows above.


Above helps in some way?

Not really. I asked for a formula, if possible. If that's not possible, an
explanation of why not would be helpful.

I guess the reason that my formula failed is that OR accepts an array
argument, which foils my attempt to have the formula interpreted as:
MIN({IF(OR(H2<K2,I2<K2,J2<K2),ROW(G2),
IF(OR(H3<K3,I3<K3,J3<K3),ROW(G3), ...}).

Having realized that, I simply need to use an alternative for OR. The
following does the job:

=MIN(IF((H3:H4889<K3:K4889)+(I3:I4889<K3:K4889)+ (J3:J4889<K3:K4889),ROW(G3:G4889)))

(Sorry for the row numbering change. I inserted a row.)


----- original message -----

"Max" wrote in message
...
In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2))
Copy down. Leave N1 empty.

(I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful)

Then you could use in say, M2: =MATCH(1,N:N,0)
to get the "first" row number which satisfies the conditions

Above helps in some way? Click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"WhatsUp31415" wrote:
Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly
braces.

Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and
copied
down to confirm that the OR function first returns TRUE in row 4.

How to find first row where condition is true?
 

Improvement

Don't really need INDEX:

=MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1

Still array entered

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Maybe this array formula** :

=INDEX(ROW(K:K),MATCH(TRUE,MMULT(--(H2:J4888=K2:K4888),{1;1;1})<3,0)+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"WhatsUp31415" wrote in message
...
Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly
braces.

Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and
copied down to confirm that the OR function first returns TRUE in row 4.

How to find first row where condition is true?
 

Ah, so. Dismiss my earlier help attempt which is clearly below your
expertise level. I have no further comments.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"WhatsUp31415" wrote in message
...
"Max" wrote:
I took the liberty to add an additional logic check that col K is not
empty, which IMO makes your original OR intents more meaningful

Why? Rhetorical question. Only I can determine the "meaningfulness" of
conditions to test. In this case, K2:K4888 is fully populated, just as
columns H, I and J are. So the extra condition is irrelevant


In N2: =--AND(K2<"",OR(H2<K2,I2<K2,J2<K2))
Copy down. Leave N1 empty.
[....]
Then you could use in say, M2: =MATCH(1,N:N,0)

Well, if I wanted to use helper cells, I would simply use
=OR(H2<K2,I2<K2,J2<K2), as I did in my original posting.
Computationally more efficient.

And I would use =ROW(M1)+MATCH(TRUE,M2:M4888,0) because I want the row
number, not simply the array index. I use ROW so that the formula is
updated automagically if I insert rows above.


Above helps in some way?

Not really. I asked for a formula, if possible. If that's not possible,
an explanation of why not would be helpful.

I guess the reason that my formula failed is that OR accepts an array
argument, which foils my attempt to have the formula interpreted as:
MIN({IF(OR(H2<K2,I2<K2,J2<K2),ROW(G2),
IF(OR(H3<K3,I3<K3,J3<K3),ROW(G3), ...}).

Having realized that, I simply need to use an alternative for OR. The
following does the job:

=MIN(IF((H3:H4889<K3:K4889)+(I3:I4889<K3:K4889)+ (J3:J4889<K3:K4889),ROW(G3:G4889)))

(Sorry for the row numbering change. I inserted a row.)

How to find first row where condition is true?
 

You cannot use logical functions in an array formula
This gives the row of the first line where H, I OR J do not equal K
=MIN(IF(((H2:H14<K2:K14)+(I2:I14<K2:K14)+(J2:J14 <K2:K14)),ROW(K2:K14),10^99))
enter as array formula, of course
change + to - to find the fist row where H, I AND J do not equal K
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"WhatsUp31415" wrote in message
...
Looking at rows 2:4888, I want to find the first row number "x" in which
OR(Hx<Kx,Ix<Kx,Jx<Kx) is true.

How can I write that formula in Excel 2003?

I tried the following array formula, to no avail:

=MIN(IF(OR(H2:H4888<K2:K4888,I2:I4888<K2:K4888,J 2:J4888<K2:K4888),ROW(G2:G4888)))

making sure that the condition is first true in row 4.

But that formula always returns 2.

I confirmed that the formula is an array formula enclosed in curly braces.

Also, I filled a parallel column with =OR(H2<K2,I2<K2,J2<K2) and copied
down to confirm that the OR function first returns TRUE in row 4.