RalphA said:
...However... (sorry), I still don't understand how it can report the position of the fourth (the last) 0 in the data range. Also, how does it determine the correct answer for the one instance of 4?
RalphA said:
...However... (sorry), I still don't understand how it can report the position of the fourth (the last) 0 in the data range. Also, how does it determine the correct answer for the one instance of 4?
Fairwinds's explanation/exposition is just about right.
=LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)
is structured as:
LOOKUP(LookupValue,LookupVector,ResultVector)
where a vector can be an array like {2,3,7,9} or range object like X3:X6.
The way the formula is set up exploits the fact that
(A)
LOOKUP(
n,Ref)
VLOOKUP(
n,Ref,1,1)
INDEX(Ref,MATCH(
n,Ref,1)
will all return the last numerical value from Ref when
n is a number that cannot occur in Ref, an example use of which is:
=LOOKUP(9.99999999999999E+307,A:A);
And the fact that
(B)
the lookup functions ignore error values.
Since the LookupVector in the formula of interest, that is,
1/($B$2:$B$13=D2),
is an array object, I should also add the fact that
(C)
the LOOKUP function is capable of returning computed arrays, without control+shift+enter (SumProduct is another example).
Understanding (A) is essential. It's the topic of a discussion in:
http://tinyurl.com/5l4j7
Since
=LOOKUP(9.99999999999999E+307,A2:A25)
returns the last
numerical value from A2:A25, which is not perse the last value, the following is an obvious extension:
=LOOKUP(9.99999999999999E+307,A2:A25,B2:B25)
will return the value from B2:B25 which is associated with the last numerical value in A2:A25. (9.99999999999999E+307 is often referred to as BigNum.)
Given the foregoing,
=LOOKUP(2,1/($B$2:$B$13=D2),$A$2:$A$13)
becomes intelligible for:
The lookup value 2 is a BigNum with respect to 1/($B$2:$B$13=D2), because 1 divided by any number cannot be equal to or greater than 2. One can replace 2, if so desired, with the BigNum itself (that is: 9.99999999999999E+307).
($B$2:$B$13=D2)
is a conditional that is bound to evaluate to an array consisting of logical values like:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
Then:
1/{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
Since 1 and 0 are Excel's numerical equivalents of TRUE and FALSE under coercion, we get a calculated array like:
{1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1}
Since the last numerical value is the 12th item in the foregoing array, LOOKUP will retrieve the 12th item from $A$2:$A$13, the ResultVector.
Let's take up the formula which corresponds to D6 housing 4 (See the exhibit in my original post)...
=LOOKUP(2,1/($B$2:$B$13=D6),$A$2:$A$13)
===>
=LOOKUP(2,1/({0;1;5;0;2;2;4;0;3;5;1;0}=4),{500;501;502;503;504;505;506;507;508;509;510;511})
===> (after the divison)
=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},{500;501;502;503;504;505;506;507;508;509;510;511})
As can be seen, the last numerical value (the last instance of 1) is the 7th item. As it so happens, the last numerical value here is also the only numerical value. Hereafter LOOKUP proceeds to fetch the 7th item from
{500;501;502;503;504;505;506;507;508;509;510;511}
which is: 506.
Hope the foregoing is filling in fairwinds's correct exposition at those places where you wanted to have more info.
