Excel - get header from first & last non empty cells in row

Question

I have the sales for items by week (D4:L6). I want to calculate the week number of the first sale and week number of the last sale (B4:C6).

I don't know how to solve this, I found ways online to get the first non empty cell in a row, last non empty cell in a row, but cant get figuring out its header

Answer 1

You could do for the First Week:

=INDEX($D$2:$L$2,XMATCH(1,SIGN($D4:$L4),0,1) )

and for the last week:

=INDEX($D$2:$L$2,XMATCH(1,SIGN($D4:$L4),0,-1) )

If you really meant Week Number, then change $D$2:$L$2 to $D$1:$L$1.

Answer 2

A simple way to solve it is by the following solution:

For First Sale (Week):

B4: {=INDEX($D$2:$L$2,MATCH(TRUE,D4:L4>0,0))}

B5: {=INDEX($D$2:$L$2,MATCH(TRUE,D5:L5>0,0))}

B6: {=INDEX($D$2:$L$2,MATCH(TRUE,D6:L6>0,0))}

In this case the formulas are matrix formulas and to enter them in the cell you must press Ctrl+Shift+Enter. The advantage is the compatibility with other versions of Office.

The MATCH function allows a reference to be made to the first cell with a value, searching from left to right. Then the INDEX function allows to return the value that corresponds to that reference but in another row (in this case Row 2, which indicates the Date).

For Last Sale (Week):

C4: { =INDEX($2:$2,MAX(COLUMN($D4:$L4)*($D4:$L4<>"")))}

C5: { =INDEX($2:$2,MAX(COLUMN($D5:$L5)*($D5:$L5<>"")))}

C6: { =INDEX($2:$2,MAX(COLUMN($D6:$L6)*($D6:$L6<>"")))}

In this case the formulas will look for the reference to the last cell with a value and return the value corresponding to that reference in Row 2, very similar to the previous case.

You can check the file here: https://docs.google.com/spreadsheets/d/1EtbISoTpCZCwCez5T2ftUEXaovf9C2Uh/edit?usp=sharing&ouid=117983986506590434361&rtpof=true&sd=true