amazing Aladin, how does the formula works?
Let BigNum = 9.99999999999999E+307
1. LOOKUP(BigNum,Reference)
yields the last numeric value from Reference, which can be an ordinary range (like A2:A4) or a calculated array (like {7,1,#VALUE!,2,#VALUE!}.
2. LOOKUP(X,Reference1,Reference2)
yields the correlate from Reference2 which is given by the position of X in Reference1.
3. LOOKUP(BigNum,Reference1,Reference2)
operates likewise and returns from Reference2 the corelate of the last numeric value of Reference1.
Example: LOOKUP(BigNum,{1,7,4},{"Kad","Nad","Jad"}) ==> Jad
4. The following are the kernel citations:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html (Post #3)
http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html (Post #7, algorithmic description)
5. FIND(X,Reference)
does a search for X in Reference, yielding an array result, something like:
{8;#VALUE!}
Note that the last numeric value is 8 and this value is at position #1.
6. FIND(X&"|",Reference&"|")
adds to X a | at the end, making the search for a substring ending with |. The same action is taken with adding a | char to Reference at its end.
So, searching 123 in 2345123768 becomes searchin for 123| in 2345123768|, which will fail (as intended). But looking for 123 in 32489123 will succeed as intended. To re-cap: The action effects a filtering of "Ends With".
7. By [3] and [6], we can write:
LOOKUP(9.99999999999999E+307,FIND($C2&"|",$A$2:$A$3&"|"),A$2:A$3)
which will determine the position of the value of C2 in $A$2:$A$3 and deliver the value in A$2:A$3 at that position.
Q.E.F.