Find first value in a range

  • Thread starter Thread starter JonoB
  • Start date Start date
J

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
 
Hi
the following array formula will work if you have only values in your
range
=INDEX(UnitPhase,MIN(IF(UnitPhase>0,ROW(UnitPhase))))
 
One way,

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

entered with ctrl + shift & enter
 
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.
 
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)
 
To get the cell reference

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

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