Simple Array formula needed ??

tbablue

Active Member
Joined
Apr 29, 2007
Messages
472
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 7, 2006
Messages
8,426
Office Version
  1. 2019
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:
Upvote 0

tbablue

Active Member
Joined
Apr 29, 2007
Messages
472
Office Version
  1. 365
Platform
  1. Windows
Great simple low-tech solution

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

:)
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top