hlookup or other method

  • Thread starter Thread starter Duplatt
  • Start date Start date
D

Duplatt

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.
 
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))

--
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.
 
RD - Thank You, it worked great-- and you are right,of course, about the
number of columns.
Appreciated -- Duane
 
Back
Top