find first nonzero value in column
 

I have two columns A and B. Column A contains ascending numbers from 1 to
100, Column B arbitrary numbers. I am looking for a function that looks for
the first nonzero value in column B (going from the first to the last row)
and then returns the value from column A of the same row. Can somebody help
me?

Thanks,
David

On Mon, 10 Jan 2005 05:07:01 -0800, "DAVID"
wrote:

I have two columns A and B. Column A contains ascending numbers from 1 to
100, Column B arbitrary numbers. I am looking for a function that looks for
the first nonzero value in column B (going from the first to the last row)
and then returns the value from column A of the same row. Can somebody help
me?

Thanks,
David

In general, the array formula:

=INDEX(A1:A100,MATCH(TRUE,B1:B100<0,0))

will do that.

To enter an array-formula, hold down <ctrl<shift while hitting <enter. XL
will place braces {...} around the formula if you did it correctly.

However, given the specifics of your question, the simplified array formula:

=MATCH(TRUE,B1:B100<0,0)

should give the same result.


--ron

=OFFSET(A1,MATCH(TRUE,ISNUMBER(B1:INDEX(B:B,MATCH( 9.99999999999999E+307,B:B))),0)-1,0,1,1)

whichmust be confirmed with control=shift+enter instead of just with enter.

If column A is populated with the sequence 1 to 100 from A1 on:

=MATCH(TRUE,ISNUMBER(B1:B100),0)

which must be confirmed with control+shift+enter (instead of the usual
enter), should suffice.

DAVID wrote:
I have two columns A and B. Column A contains ascending numbers from 1 to
100, Column B arbitrary numbers. I am looking for a function that looks for
the first nonzero value in column B (going from the first to the last row)
and then returns the value from column A of the same row. Can somebody help
me?

Thanks,
David

Ron Rosenfeld wrote...
"DAVID" wrote...
I have two columns A and B. Column A contains ascending numbers from
1 to
100, Column B arbitrary numbers. I am looking for a function that
looks for
the first nonzero value in column B (going from the first to the last
row)
and then returns the value from column A of the same row. Can
somebody help
me?
....
However, given the specifics of your question, the simplified array
formula:

=MATCH(TRUE,B1:B100<0,0)

should give the same result.
Where did the OP state that the col A values were in A1:A100?

On 10 Jan 2005 12:36:34 -0800, wrote:

Where did the OP state that the col A values were in A1:A100?

I've always assumed that posters are intelligent enough to alter range
references appropriately.


--ron

Maybe via an inference from

"Column A contains ascending numbers from 1 to 100"

Though of course there could be duplicates and/or blanks.

On 10 Jan 2005 12:36:34 -0800, wrote:

Ron Rosenfeld wrote...
"DAVID" wrote...
I have two columns A and B. Column A contains ascending numbers from
1 to
100, Column B arbitrary numbers. I am looking for a function that
looks for
the first nonzero value in column B (going from the first to the last
row)
and then returns the value from column A of the same row. Can
somebody help
me?
...
However, given the specifics of your question, the simplified array
formula:

=MATCH(TRUE,B1:B100<0,0)

should give the same result.
Where did the OP state that the col A values were in A1:A100?