The month of the first sale

[Speer]

Hi Guys,

Please need your help. I've got data table, the first column with
store numbers, the top row with months for 2 years, and actual data.
The question is how to find the month of the first sale for each store?

I was thinking to use pivot, but still don’t realise how to find the first sale month.

Thank you )

 

[kchiba]

Hi Speer,

In the months where you did not have sales are the cells blank or do they have zero?

Please upload a sample of the data, so that we can see where you want the result of the formula

regards

kanti

 

[Speer]

Hi Kanti,

Thank you. The cells are blank. Please see uploaded sample

 

[kchiba]

Check the uploaded file.

kanti

 

[r1c1]

@Speer...

You can use this formula...

=INDEX($B$1:$V$1,MATCH(TRUE,(B2:V2)>0,0))

Press CTRL+Shift+Enter to get the result.

PS: Just saw Kanti's solution. Good one

 

[Speer]

Thanks Kanti, all good.

r1c1 thank you too, i dont use a lot array formulas, good to know.

 

[Sajan]

I would suggest going with Chandoo's or Kanti's solutions... but in the interest of showing one more approach... (after all, that is what makes Excel interesting!)

=HLOOKUP(1,IF({1;0},N(C2:W2>0),C$1:W$1),2,0)
enter with Ctrl + Shift + Enter

Cheers,
Sajan.