VIP
Trusted Members
June 25, 2016
VIP
April 21, 2015
VIP
Trusted Members
June 25, 2016
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
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)