Aug 21 2022 07:14 PM - edited Aug 21 2022 07:54 PM
Aug 21 2022 07:39 PM
Aug 21 2022 07:55 PM
Aug 21 2022 09:29 PM
Aug 22 2022 03:32 AM
In a cell in row 3:
=LET(i, MATCH(TRUE,D3:AM3>0,0), m, INDEX($D$2:$AM$2,i), y, INDEX($D$1:$AM$1,QUOTIENT(i-1,12)*12+1), m &", "&y)
Fill down.
Aug 23 2022 12:27 AM
SolutionThe formula that I posted will only work in Excel 365 / 2021. The following version should work in older versions too:
=INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) & ", " & INDEX($D$1:$AM$1,QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1)
Confirm with Ctrl+Shift+Enter.
Aug 23 2022 08:40 PM
Aug 23 2022 11:37 PM
Aug 23 2022 11:49 PM
The first part INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) looks up the month.
D3:AM3>0 returns an array of TRUE/FALSE values: TRUE for each cell in D3:AM3 with a value > 0, and FALSE for each cell that contains 0.
MATCH(TRUE,D3:AM3>0,0) searches for TRUE and returns the position of the first TRUE value in this array. That corresponds to the first month with a value > 0.
INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) looks up the name of that month in D2:AM2. We use the absolute reference $D$2:$AM$2 to ensure that it will remain the same when we fill down the formula.
The second part INDEX($D$1:$AM$1,QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1) uses the same idea to find the year, but it is more complicated because the year appears in intervals of 12 cells in D1:AM1.
MATCH(TRUE,D3:AM3>0,0) once again returns the position of the first value > 0, i.e. the first month with a value > 0.
The year occurs in the first cell of each set of 12 cells, so we check how many times 12 goes into the position returned by MATCH, then multiply by 12, so we get 1 or 13 or 25 etc. INDEX then looks up the year in $D$1:$AM$1.
Finally, the month and year are concatenated with ", " in between.
Aug 25 2022 01:11 AM
Aug 23 2022 12:27 AM
SolutionThe formula that I posted will only work in Excel 365 / 2021. The following version should work in older versions too:
=INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) & ", " & INDEX($D$1:$AM$1,QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1)
Confirm with Ctrl+Shift+Enter.