locate first value greater than

[MPI Planner]

I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

 

[Luke M]

=MIN(IF(B2:F2>0,COLUMN(B2:F2),FALSE))
Input this as an array (Use Ctrl+Shift+Enter). If done right, you should see
curly brackets {} appear around your formula.

 

[T. Valko]

Another one:

=INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F2>0,0),0))

Format as Date

 

[Bernard Liengme]

The UDF called with, fro example, =Tryme(B2:Q2) will return 3/16
best wishes

Function tryme(myrange)
mylast = myrange.Count
tryme = "No non-zero"
For Each mycell In myrange
If mycell.Value <> 0 Then
tryme = mycell.Offset(-1, 0)
Exit For
End If
Next
End Function

 

[Ron Rosenfeld]

I would like to be able to return the first column that has a number greater
than zero. For instance

3/2 3/9 3/16 3/23 3/30
Product A 0 0 12 0 20

How could I get a result that either returned 3/16 as the first date or
column 4 as the first instance?

With dates in row 1, and data in row 2:

=MATCH(1,INDEX(ISNUMBER(2:2)*(2:2>0),0),0) --> 4

=INDEX(1:1,,MATCH(1,INDEX(ISNUMBER(2:2)*(2:2>0),0),0)) --> 3/16


--ron

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter)

=INDEX(C7:H8,1,MATCH(TRUE,D8:H8>0,0)+1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[MPI Planner]

This worked great. How can I add a lookup to this? I would like to be able to
reference a cell and find that value in the table to return the same data.

vlookup(z1,a1:y1000,INDEX(B1:F1,MATCH(TRUE,INDEX(B2:F2>0,0),0)))?