# Simple Array formula needed ??

#### tbablue

##### Active Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Special-K99

##### Well-known Member
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

Last edited:

#### tbablue

##### Active Member
Great simple low-tech solution

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

Replies
2
Views
1K
Replies
2
Views
216
Replies
1
Views
723
Replies
20
Views
788
Replies
6
Views
312

1,191,274
Messages
5,985,698
Members
439,974
Latest member
sjoerdbosch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back