Simple Array formula needed ??

[tbablue]

Hi

In cells H1:AX1 I have a number of values like 200901, 200902, 200903 (year2009 wk01, wk02, wk03, etc)

In cells H52:AX52 I have a number of values (simple integers) and zeros '0'

In cell E54 I'd like to return the corresponding date vale from row 1 (H1:AX1) when the first value above zero is found in row 52 (H52:AX52).

Also

In cell E53 I'd like to return the corresponding date vale from row 1 (H1:AX1) when the last value above zero is found in row 52 (H52:AX52).

Can anyone help me? I think I need an simple array formula.

Cheers

 

[Special-K99]

Dont think you need an array formula. Try this...

Add an extra row in say H53:AX53 with this formula in each column H to AX

H53 =IF(H52=0,0,1)

The function will return 0 for zero values and 1 for non zero values

Copy the cell H53 along the row to AX53

Then just match on the first 1 you find in that row using

In E54 enter

=INDEX(H1:AX53,1,MATCH(1,H53:AX53,0))

E54 should now contain the first non zero year


If you copy the years and value rows to another part of the spreadsheet but put the columns in reverse, ie H54 = AX1, I54 = AW1 etc you can use a copy of the same formula to determine the last non zero year

 

[tbablue]

Great simple low-tech solution

Can't believe I didn't see that one. Thanks for your attention Special.