lookup nonzerovalues
 

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?

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

"DAVID" wrote in message
...
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?

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.

DAVID wrote:

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?

--

Dave Peterson