I have a row of cells, for instance A1:BZ1, which can be blank or
number. I am able to output the right most cell with the formula:

LOOKUP(9.99999999999999E+307,A1:BZ1)

Is there a similar formula that allows outputting the left most cell
that contains a number?


Thank you in advanced.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=504215

On Mon, 23 Jan 2006 16:10:40 -0600, Morrigan
wrote:


I have a row of cells, for instance A1:BZ1, which can be blank or
number. I am able to output the right most cell with the formula:

LOOKUP(9.99999999999999E+307,A1:BZ1)

Is there a similar formula that allows outputting the left most cell
that contains a number?


Thank you in advanced.

One method:

Use the *array* formula:

=INDEX(A1:BZ1,1,MATCH(TRUE,ISNUMBER(A1:BZ1),0))

To enter an array formula, after typing or pasting it into the cell, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula.


--ron

Ron Rosenfeld Wrote:
On Mon, 23 Jan 2006 16:10:40 -0600, Morrigan
wrote:


I have a row of cells, for instance A1:BZ1, which can be blank or
number. I am able to output the right most cell with the formula:

LOOKUP(9.99999999999999E+307,A1:BZ1)

Is there a similar formula that allows outputting the left most cell
that contains a number?


Thank you in advanced.

One method:

Use the *array* formula:

=INDEX(A1:BZ1,1,MATCH(TRUE,ISNUMBER(A1:BZ1),0))

To enter an array formula, after typing or pasting it into the cell,
hold down
<ctrl<shift while hitting <enter. Excel will place braces {...}
around the
formula.


--ron



Thanks it works well.

I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?

Thanks again.


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=504215

You could try the lookup formula, but with a very small number, eg
1E-200.

Hope this helps.

Pete

I think LOOKUP returns #N/A if lookup-value is smaller than the smallest
value.


Pete Wrote:
You could try the lookup formula, but with a very small number, eg
1E-200.

Hope this helps.

Pete


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=504215

On Mon, 23 Jan 2006 16:43:00 -0600, Morrigan
wrote:

Thanks it works well.

I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?

Thanks again.

Possibly.

You could always write a UDF in VBA.
--ron

Morrigan Wrote:

I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?


=INDEX(A1:BZ1,MATCH(1,INDEX(ISNUMBER(A1:BZ1)+0,0), 0))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=504215

On Mon, 23 Jan 2006 17:38:57 -0600, daddylonglegs
wrote:


Morrigan Wrote:

I personally try to avoid using array formula. Is there a different
approach that does not require the use of array formula?


=INDEX(A1:BZ1,MATCH(1,INDEX(ISNUMBER(A1:BZ1)+0,0) ,0))

Nice
--ron

daddylonglegs Wrote:
=INDEX(A1:BZ1,MATCH(1,INDEX(ISNUMBER(A1:BZ1)+0,0), 0))


Super, thank you~


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=504215