Log In
Register
Home
October 11, 2012
Offline
Dear Friends
I want to know in which month last amount is paid. Kindly help me to put formula in column O to get the desired result.
Best Regards
Sohail
VIP
Trusted Members
June 25, 2016
Offline
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
October 11, 2012
Offline
Its Working....... Thank you so much Sunny.
VIP
April 21, 2015
Offline
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?
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
April 21, 2015
Offline
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
1 Guest(s)
