hlookup or other method

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Duplatt - 30 Mar 2008 20:32 GMT

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.

Reply to this Message

Ragdyer - 30 Mar 2008 21:19 GMT

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:BI1>0,0))

Signature

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 !
---------------------------------------------------------------------------

> I have a spread sheet of 100 rows & 60 columns
> Bottom row is years 2008 thru 2068 -- Range A1:BH100
[quoted text clipped - 7 lines]
> I have tried lookup, hlookup & match but probably am not doing something
> right.

Reply to this Message

Duplatt - 31 Mar 2008 04:41 GMT

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

> First of all, you'd need 61 columns to go from 2008 to 2068.
> That brings you out to BI100, not BH100!
[quoted text clipped - 17 lines]
> > I have tried lookup, hlookup & match but probably am not doing something
> > right.

Reply to this Message

RagDyeR - 31 Mar 2008 15:42 GMT

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

Signature

Regards,

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

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!
[quoted text clipped - 20 lines]
> > I have tried lookup, hlookup & match but probably am not doing something
> > right.

Reply to this Message

Rate this thread: