Find first value in a range

[JonoB]

Hi,
I have a range called UnitPhase that is one column of data. I need
formula that will return the first cell that is greater than zero.

I have tried some array formulas but cant get it to work, such a
{=INDEX(UnitPhase,MIN((UnitPhase<>"0")*ROW(UnitPhase)))}

I bviously dont have the correct syntax, so any help is muc
appreciated

 

[Frank Kabel]

Hi
the following array formula will work if you have only values in your
range
=INDEX(UnitPhase,MIN(IF(UnitPhase>0,ROW(UnitPhase))))

 

[Don Guillett]

try using match with the 0 to look down column I
=MATCH(0.001,I:I,0)

 

[Peo Sjoblom]

One way,

=INDEX(UnitPhase,MATCH(TRUE,UnitPhase>0,0))

entered with ctrl + shift & enter

 

[JonoB]

Thanks guys, both

=INDEX(UnitPhase,MIN(IF(UnitPhase>0,ROW(UnitPhase))))

and

=INDEX(UnitPhase,MATCH(TRUE,UnitPhase>0,0))

work 100%.

If I wanted to return the actual cell reference, I could do a lookup on
the above result, but I suspect that this could actually be achieved in
the array formula.

Once again, thanks for the help.

 

[Frank Kabel]

Hi
if you only want the row number use either the array formula
=MIN(IF(UnitPhase>0,ROW(UnitPhase)))

or
MATCH(TRUE,UnitPhase>0,0)

 

[Peo Sjoblom]

To get the cell reference

=CELL("address",INDEX(UnitPhase,MATCH(TRUE,UnitPhase>0,0)))

note that match formula should be faster than (min * row)