hlookup or other method
 

I have a spread sheet of 100 rows & 60 columns
Bottom row is years 2008 thru 2068 -- Range A1:BH100
Columns have numbers scattered randomly. Most numbers are 0
Some rows have only one column with a number, others have several columns
with numbers. The numbers are of different values and are not in ascending or
decending value.
How can I start in a1 and locate the first column,in the row, with a number
greater than 0 and then identify the year? I need to start with " If(A1 = 0
----.
I have tried lookup, hlookup & match but probably am not doing something
right.

hlookup or other method
 

First of all, you'd need 61 columns to go from 2008 to 2068.
That brings you out to BI100, not BH100!

Try this *array* formula in BJ1:

=INDEX(A$100:BI$100,MATCH(TRUE,A1:BI10,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy the formula down to BJ100.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Duplatt" wrote in message
...
I have a spread sheet of 100 rows & 60 columns
Bottom row is years 2008 thru 2068 -- Range A1:BH100
Columns have numbers scattered randomly. Most numbers are 0
Some rows have only one column with a number, others have several columns
with numbers. The numbers are of different values and are not in ascending
or
decending value.
How can I start in a1 and locate the first column,in the row, with a
number
greater than 0 and then identify the year? I need to start with " If(A1 =
0
----.
I have tried lookup, hlookup & match but probably am not doing something
right.

hlookup or other method
 

RD - Thank You, it worked great-- and you are right,of course, about the
number of columns.
Appreciated -- Duane

"Ragdyer" wrote:

First of all, you'd need 61 columns to go from 2008 to 2068.
That brings you out to BI100, not BH100!

Try this *array* formula in BJ1:

=INDEX(A$100:BI$100,MATCH(TRUE,A1:BI10,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy the formula down to BJ100.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Duplatt" wrote in message
...
I have a spread sheet of 100 rows & 60 columns
Bottom row is years 2008 thru 2068 -- Range A1:BH100
Columns have numbers scattered randomly. Most numbers are 0
Some rows have only one column with a number, others have several columns
with numbers. The numbers are of different values and are not in ascending
or
decending value.
How can I start in a1 and locate the first column,in the row, with a
number
greater than 0 and then identify the year? I need to start with " If(A1 =
0
----.
I have tried lookup, hlookup & match but probably am not doing something
right.

hlookup or other method
 

You're welcome, and your feed-back is appreciated.
--

Regards,

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

"Duplatt" wrote in message
...
RD - Thank You, it worked great-- and you are right,of course, about the
number of columns.
Appreciated -- Duane

"Ragdyer" wrote:

First of all, you'd need 61 columns to go from 2008 to 2068.
That brings you out to BI100, not BH100!

Try this *array* formula in BJ1:

=INDEX(A$100:BI$100,MATCH(TRUE,A1:BI10,0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead
of
the regular <Enter, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy the formula down to BJ100.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Duplatt" wrote in message
...
I have a spread sheet of 100 rows & 60 columns
Bottom row is years 2008 thru 2068 -- Range A1:BH100
Columns have numbers scattered randomly. Most numbers are 0
Some rows have only one column with a number, others have several
columns
with numbers. The numbers are of different values and are not in
ascending
or
decending value.
How can I start in a1 and locate the first column,in the row, with a
number
greater than 0 and then identify the year? I need to start with " If(A1
=
0
----.
I have tried lookup, hlookup & match but probably am not doing something
right.