How can I use a formula to return the first number in a range?

[Husker87]

I have a range A1:A10. I’m looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number. Thoughts??

 

[T. Valko]

Try this array formula** :

=INDEX(A1:A10,MATCH(TRUE,ISNUMBER(A1:A10),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Ragdyer]

Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>0,0),0))

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10>0,0),0))

Depending on what you're looking for.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I have a range A1:A10. I’m looking for a formula to put in B1 that will
return the first number in my range A1:A10 when searching from top to bottom.
Sometime the first number appears in A1 and sometimes not until A4 with
other numbers in A7 and A8. I only want A4 or the first number.

Thoughts??

 

[Shane Devenshire]

Hi,

Here is an array formula:

=INDEX(A1:A10,MATCH(1,--(A1:A10*1>0),0))
or
=INDEX(A1:A10,MATCH(TRUE,A1:A10*1>0,0))

To make it an array you enter it by pressing Shift+Ctr+Enter

 

[Shane Devenshire]

I should add that this returns the first non-zero number.

 

[Shane Devenshire]

A little bit more -

For the first number (including 0)

=INDEX(A1:A10,MATCH(1,--ISNUMBER(A1:A10),))

and you can shorten my previous formula a tad for the non-zero situation

=INDEX(A1:A10,MATCH(1,--(A1:A10*1>0),))

both are array entered.

 

[Husker87]

THANKS. That worked. Have a great day.

Non-array suggestions:

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10<>0,0),0))

=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10>0,0),0))

Depending on what you're looking for.

 

[Shane Devenshire]

Hi,

I think both of these suggestions will return the first text entry if there
is one before the first number.

Here are two non-array solution which avoid text entries:

First Non-Zero number - non array:
=INDEX(A1:A10,MATCH(TRUE,INDEX(A1:A10*1>0,),))

First Number - non array:
=INDEX(A1:A10,MATCH(TRUE,INDEX(ISNUMBER(A1:A10),),))

 

[Harlan Grove]

Try this array formula** :

=INDEX(A1:A10,MATCH(TRUE,ISNUMBER(A1:A10),0))

....

Could avoid array entry with

=INDEX(A1:A20,MATCH(TRUE,INDEX(ISNUMBER(A1:A20),0),0))

 

[Ragdyer]

Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:

=INDEX(A1:A20,MATCH(1,INDEX((ISNUMBER(A1:A20))*(A1:A10>0),),))

 

[Ragdyer]

You're welcome, and appreciate the feed-back.

Hope you see all the other options your question has generated.

 

[Ragdyer]

TYPO - must equalize the ranges!

=INDEX(A1:A10,MATCH(1,INDEX((ISNUMBER(A1:A10))*(A1:A10>0),),))

 

[Harlan Grove]

Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:

....

Since when is zero not a number? Since when are negative numbers not
nonzero numbers?

Where did the OP give any hint that s/he only wanted positive numbers?

 

[RagDyeR]

Strictly an academic exercise Harlan.

The archives could benefit ... no?

---

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Besides bypassing text, why not add the other alternative of *also*
bypassing zeroes:

....

Since when is zero not a number? Since when are negative numbers not
nonzero numbers?

Where did the OP give any hint that s/he only wanted positive numbers?