Hi Suhail

Try this. In cell P2 enter  =LOOKUP(2,1/(C2:N2<>0),$C$1:$N$1)

Copy down as desired.

Hope this helps.

Sunny

Its Working....... Thank you so much Sunny. 

Hi Sunny. I see that it works fine. Can you explain the 'how and why'?

Why the '2', why the 1 divided by C2:N2 (if that's what's he doing).

And how come that <> 0 works this way?

It looks like a clever but kind of extended solution in this formula?

Hi Frans

I will try to explain whatever I can on this one. I will use the F9 key to select and evaluate the formulas part by part.

=LOOKUP(2,1/(C2:N2<>0),$C$1:$N$1)

Select (C2:N2<>0)  and press the F9 key. You will see a bunch of TRUE and FALSE for the cells that meets the condition.

=LOOKUP(2,1/{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE},$C$1:$N$1)

If you now divide 1 with these, it will give you either a 1 or #DIV/0!

=LOOKUP(2,{1,1,1,1,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},$C$1:$N$1)

$C$N:$N$1 will give you {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}

The whole evaluated formula will look like this.

=LOOKUP(2,{1,1,1,1,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"})

LOOKUP will search the value 2 from {1,1,1,1,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}

Since there is no 2 (you can enter any number > 1) , it will find the position of the last occurrence of 1.

In this example, it is the 5th position and corresponds to May {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}

I hope my explanation is clear enough.

Sunny

Thanks Sunny, nice to get such an explanation!

I did the F9 'trick' myself before asking you and it didn't make sense to me. But now I think I understand a little more: first the dividing trick is to switch from true or #div/0!  to a number I think? And then the most clever one is that because you look for the 2, it will come up with the last found 1, right?

And then it will return that position in the row you gave at last.

This really is nice to know. Who 'invented' this way of thinking? 😉

Thanks!

Frans