lookup nonzerovalues

[Guest]

I have two columns A and B. Column A contains ascending numbers from 1 to
100, column B contains arbitrary values (not in ascending order). What is the
function that looks for the first nonzero value in column B (going from the
frist to the last row) and returns the value of column A of the same row?

 

[JulieD]

Hi David

=INDEX($A$1:$B$1000,SMALL(IF($B$1:$B$1000<>0,ROW($B$1:$B$1000),""),1),1)

entered using control & shift & enter not just enter

Cheers
JulieD

 

[Dave Peterson]

Another one:

=INDEX($A$1:$A$1000,MATCH(TRUE,$B$1:$B$1000<>0,0))
Still hit ctrl-shift-enter with this array formula.

Change that 1000 to be as big as you need, but you can't use the whole column.