find first nonzero value in column

[Guest]

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]

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

 

[Aladin Akyurek]

=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.

 

[hrlngrv]

Ron Rosenfeld wrote...

....
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?

 

[Ron Rosenfeld]

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

 

[Myrna Larson]

Maybe via an inference from

"Column A contains ascending numbers from 1 to 100"

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