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

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

CodePudding user response：

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.

CodePudding user response：

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.

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<>"")))}