# Find first non-zero value in column

### Question

• Hi,

Using Excel 97. I have a long list of number values and I'd like to return the first non-zero value. I searched on Google, and came up with this formula:

=INDEX(E2:E16390,SMALL(IF(E2:E16390>0,COLUMN(E2:E16390)),1),1)

which I have entered using CTRL+SHIFT+ENTER

The first non-zero value is actuaslly on row 707, but the above formula comes back with "0". The value in cell E707 is actually "71" so I'd like that to be returned.

Alain

Thursday, September 22, 2011 11:59 AM

• On Thu, 22 Sep 2011 12:13:37 +0000, Alain Dekker wrote:

>Thanks. When I try you;re formula:
>
>=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))
>
>and then use the CTRL+SHIRT+ENTER to enter the forumla, I get the weird value of "1.385869565".
>Not sure what I'm doing wrong here. What is "**array-entered**"? Isn't my range "E2:E16390" what is required here?
>
>Thanks,
>Alain

Array-entered means to enter the formula as an array formula.  As I wrote, if you do that correctly, Excel will place braces   {...} around your formula.

The value 1.385869565 is likely someplace between E2:E707  (where you wrote you thought the first non-zero value was located).

For troubleshooting, try the following array-entered formula:

=MATCH(TRUE,E2:E16390<>0,0)

It should return the row number that it is matching.  Then select that row in Column E, and see what is in the Formula Bar.

Ron
Thursday, September 22, 2011 12:50 PM

### All replies

• On Thu, 22 Sep 2011 11:59:22 +0000, Alain Dekker wrote:

>
>
>Hi,
>
>Using Excel 97. I have a long list of number values and I'd like to return the first non-zero value. I searched on Google, and came up with this formula:
>
>=INDEX(E2:E16390,SMALL(IF(E2:E16390>0,COLUMN(E2:E16390)),1),1)
>
>which I have entered using CTRL+SHIFT+ENTER
>
>The first non-zero value is actuaslly on row 707, but the above formula comes back with "0". The value in cell E707 is actually "71" so I'd like that to be returned.
>
>Alain

This formula must be **array-entered**:

=INDEX(MyRange,MATCH(TRUE,MyRange<>0,0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>.  If you did this
correctly, Excel will place braces {...} around the formula.

Ron
Thursday, September 22, 2011 12:09 PM
• Thanks. When I try you;re formula:

=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))

and then use the CTRL+SHIRT+ENTER to enter the forumla, I get the weird value of "1.385869565".
Not sure what I'm doing wrong here. What is "**array-entered**"? Isn't my range "E2:E16390" what is required here?

Thanks,
Alain

<Ron Rosenfeld> wrote in message news:9ecea888-d7b6-4edb-bf2c-1013ba8475eb@communitybridge.codeplex.com...

On Thu, 22 Sep 2011 11:59:22 +0000, Alain Dekker wrote:

Hi,

Using Excel 97. I have a long list of number values and I'd like to return the first non-zero value. I searched on Google, and came up with this formula:

=INDEX(E2:E16390,SMALL(IF(E2:E16390>0,COLUMN(E2:E16390)),1),1)

which I have entered using CTRL+SHIFT+ENTER

The first non-zero value is actuaslly on row 707, but the above formula comes back with "0". The value in cell E707 is actually "71" so I'd like that to be returned.

Alain

This formula must be **array-entered**:

=INDEX(MyRange,MATCH(TRUE,MyRange<>0,0))

----------------------------------------

To *array-enter* a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

Ron

Thursday, September 22, 2011 12:13 PM
• On Thu, 22 Sep 2011 12:13:37 +0000, Alain Dekker wrote:

>Thanks. When I try you;re formula:
>
>=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))
>
>and then use the CTRL+SHIRT+ENTER to enter the forumla, I get the weird value of "1.385869565".
>Not sure what I'm doing wrong here. What is "**array-entered**"? Isn't my range "E2:E16390" what is required here?
>
>Thanks,
>Alain

Array-entered means to enter the formula as an array formula.  As I wrote, if you do that correctly, Excel will place braces   {...} around your formula.

The value 1.385869565 is likely someplace between E2:E707  (where you wrote you thought the first non-zero value was located).

For troubleshooting, try the following array-entered formula:

=MATCH(TRUE,E2:E16390<>0,0)

It should return the row number that it is matching.  Then select that row in Column E, and see what is in the Formula Bar.

Ron
Thursday, September 22, 2011 12:50 PM
• Thanks! I was pretty sure I was using CTRL+SHIFT+ENTER (to get Excel to put the baces around the formula) but your troubleshooting helped solve the problem. By running your formula first, then building it slowly up I got it to work:

=INDEX(E2:E16390,MATCH(TRUE,E2:E16390<>0,0))

I then put the cursor in the Formular Bar and pressed CTRL+SHIFT+ENTER and it works.
Thanks again,
Alain

Thursday, September 22, 2011 1:19 PM
• Glad to help.  Thanks for the feedback.
Ron
Thursday, September 22, 2011 1:28 PM