How to find first occurance based on given condition

[Manamana]

I like to get the position of first non-zero number out of a data range like this:

0 0 0 0 5 4 0 6 0

I am after result of 5 through any formula. Please help!

 

[Sektor]

UDF.

[COLOR="Blue"]Function[/COLOR] NonZero(Str [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]) [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]With[/COLOR] CreateObject("VBScript.RegExp")
        .Pattern = "[1-9]"
        NonZero = .Execute(Str)(0).FirstIndex + 1
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Function[/COLOR]

 

[Aladin Akyurek]

I like to get the position of first non-zero number out of a data range like this:

0 0 0 0 5 4 0 6 0

I am after result of 5 through any formula. Please help!

One of...

Control+shift+enter, not just enter:

=MATCH(TRUE,A2:I2>0,0)

=MATCH(1,IF(ISNUMBER(A2:I2),IF(A2:I2>0,1)),0)

The latter would be adequate if there are text-valued cells in the range of interest.

If there are also negative numbers...

=MATCH(1,1-(A2:I2=0),0)

=MATCH(1,IF(ISNUMBER(A2:I2),IF(1-(A2:I2=0),1)),0)

 

[mikerickson]

Perhaps,

=MATCH(0, A1:J1) + 1

 

[Manamana]

Thanks, Guys. You are geniuses! I have tried all suggested methods. Apart from Aladin's solution, others all work well. Don't know why, with Aladin's solution, there is always #value error. Anyway, thank you all! This is my first post in this forum. So far so good. Will keep coming back...

 

[Aladin Akyurek]

Thanks, Guys. You are geniuses! I have tried all suggested methods. Apart from Aladin's solution, others all work well. Don't know why, with Aladin's solution, there is always #value error. Anyway, thank you all! This is my first post in this forum. So far so good. Will keep coming back...

Funny...

You need to confirm the formulas I suggested with control+shift+enter, not just enter. Formulawise,
they offer you exactly what you need.

 

[Manamana]

Hi, Guys, I found when it comes to a data range starting with a non-zero data, match formula will result in the total number of the range.
I.e.
2 1 0 0 6 1 0
Match (0,A1:A7,0) = 7

Where I am looking for 1 as a result. How can I fix the problem?

 

[Manamana]

Thanks, Aladin. It works now.

 

[Aladin Akyurek]

Thanks, Aladin. It works now.

You are welcome. Thanks for providing feedback.

 

[Maheshp]

Hi Aladin

what is the reason of using Control+shift+enter when i use array formula, what is the difference between normal formula & array formula.