Lookup cell above next value >0

[hdf]

I have a table with two rows, 33 columns long. Top row has Years (2004
to 2036) in it and the row below is intended for the inputing of values
in percentage form. Some of the cells in bottom row may be left blank
or with 0 value.

Elsewhere on my spreadsheet I want to create formulas that will return
the top row value (year) for each year where the value in bottom row is
greater than 0.

For example, if

A1 = 2004 A2 = 2005 A3 = 2006 A4 = 2007
B1 = 0 % B2 = 5 % B3 = blank B4 = 20%

What formula in cell C1 will return value "2005" and in cell C2 the
next year value for which the cell in row B is not blank or 0, hence in
this case "2007".

Thank you,

Hector

 

[Bernie Deitrick]

Hector,

Your words and layout (and cell assignment descriptions) don't match, so
either use the array formula (entered with Ctrl-Shift-Enter)

=INDEX(A:A,SMALL(IF($B$1:$B$33>0,ROW($B$1:$B$33),1000),ROW()))

or the array formula (also entered with Ctrl-Shift-Enter)
=INDEX(1:1,SMALL(IF($A$2:$AG$2>0,COLUMN($A$2:$AG$2),1000),COLUMN()))

The first one can be copied down, the second copied across.

HTH,
Bernie
MS Excel MVP

 

[Fable]

Hola,

The fomula your looking for is LOOKUP
=LOOKUP(A4,A1:AG2)

place the formula above in B4

in A4 just enter the Year (e.g. 2007) and it will bring back the
(e.g. 20%)

LOOKUP is a very powerful formula, as is INDEX, MATCH. Excel can giv
more detailed info about theses. Hope it helps

Suerte