# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Find the first value in a row greater than zero

## Algeraist

Hi,
I'm trying to find the column number of the first cell in a row that is greater than zero.

There are 31 columns of of data, so if statments aren't working for me.

any suggestions

Example 
   1  2  3  4  5  6  7  8   9  
A  0 0  0  0  0  0  8  1   0  = 7
B  0 0  2  0  0  0  0  0   0  = 3
C  0 0  0  0  0  0  0  0   1  = 9

Thanks :Confused:

----------


## DonkeyOte

You could use an Array

AF1: =MATCH(TRUE,$A1:$AE1>0,0)
*confirmed with CTRL + SHIFT + ENTER*

copy down as required

EDIT: I spotted from a daddlylonglegs post a way to use INDEX within the MATCH and thereby avoid need for Array...

*AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)*

----------


## twointum

> You could use an Array
> *AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)*



This is glorious, I use it all the time, thank you!

----------


## dreich

Fantastic! That is very useful!

----------


## patryk899

> You could use an Array
> 
> AF1: =MATCH(TRUE,$A1:$AE1>0,0)
> *confirmed with CTRL + SHIFT + ENTER*
> 
> copy down as required
> 
> EDIT: I spotted from a daddlylonglegs post a way to use INDEX within the MATCH and thereby avoid need for Array...
> 
> *AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)*



Thanks a lot for saving my ***!

----------


## FortuneSyn

> *AF1: =MATCH(TRUE,INDEX($A1:$AE1>0,0),0)*



Could someone explain to me why this works?

----------


## sktneer

> Could someone explain to me why this works?



OK. Lets take a small example to show you how this works. Say the formula is MATCH(TRUE,INDEX($A1:$E1>0,0),0). And assuming that you have values 0	0  0	8  7 in the range A1:E1.

First of all the INDEX($A1:$E1>0,0) gets executed. It will return the array of True and False as you are using 0 as row number so instead of returning a single value it will return the whole array like this.....
*{FALSE,FALSE,FALSE,TRUE,TRUE}*

So your formula Match formula becomes 
*=MATCH(TRUE,{FALSE,FALSE,FALSE,TRUE,TRUE},0)*

Now in the Match function, the *lookup value* is *TRUE* and *lookup array* is *{FALSE,FALSE,FALSE,TRUE,TRUE}* and *0* is for *exact match*. So the Match will return the position of first TRUE in the array {FALSE,FALSE,FALSE,TRUE,TRUE} which is 4.
Therefore the formula will return 4 since this is the position of first value which is greater than 0 in the range A1:A1.

I hope this helps.

----------


## chirag050675

Dear All
this is really useful & amazing
Regards,
Chirag

----------


## SilverBullet17

This is awesome - thanks all!

----------


## PAP069

Lets say then the values continue to be positive and then 0 again.
How i can find the position of this second occurence?

Example 
1 2 3 4 5 6 7 8 9 
A 0 0 0 0 0 0 8 1 0 = 9

----------


## XOR LX

*@PAP069*

Can you clarify with a few more examples, together with expected results?

Regards

----------


## Pepe Le Mokko

*Administrative Note:*

At PAP . since you have already created a new thread, there is no need to continue this one
https://www.excelforum.com/excel-for...ive-value.html

----------


## Ceasefire3

SKTNeer

I literally made an account just to thank you for your explanation. I've been spending the last 2 hours banging my head against a wall with some excel stuff and your explanation helped greatly.

Thank you so ******* much.

----------


## sktneer

> SKTNeer
> 
> I literally made an account just to thank you for your explanation. I've been spending the last 2 hours banging my head against a wall with some excel stuff and your explanation helped greatly.
> 
> Thank you so ******* much.



Welcome to the forum!
Glad you found it helpful. :Smilie:

----------

