Thank you so much aladin,
Can you please explain it to me why this formula works, so that I can explain it thoroughly to others
A. LOOKUP and kindred functions ignore error values in the references they are fed with if they possibly can. (A reference consisting solely of error values will cause such a function to return an error.)
B. LOOKUP and kindred functions with match-type set to 1 recruit most likelily some form of binary search (BS) algorithm, which is very fast.
C. When given a look up value, which is
MAX(reference)+Delta
where Delta is a very small number, in order to locate that value in the reference, LOOKUP hits the last numeric value in that reference, as a side of BS. Thus:
LOOKUP(MAX(A2:A4)+Delta,A2:A4)
will deliver
4
when A2:A4 houses say 6, 0.2,
4.
D. Since MAX is slow and returns error whenever an error value occurs in the reference it looks at, we choose a value improble to occur in the references of interest to us:
9.99999999999999E+307
So we get a construct like this:
LOOKUP(9.99999999999999E+307,Reference)
This big number is a constant of Excel itself, therefore a reliable, recognizable standard. See for more:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
One of the formulas we are settled for is:
=LOOKUP(9.99999999999999E+307,1/ISNUMBER(1/A2:E2),A2:E2)
>> (re-written)
=LOOKUP(9.99999999999999E+307,X,Y)
correlates the last numeric value of X with the value of Y that is at the same position.
The last numeric value of X must be a non-zero value (the requirement).
ISNUMBER(1/A2:E2) yields TRUE for values different from 0, otherwise FALSE.
1/ISNUMBER(1/A2:E2) yields a
number for TRUE's and a #DIV/0! for FALSE's. LOOKUP correlates the position of the last number from X with a number at the same position in Y, which is A2:E2.
For more, see also:
http://www.mrexcel.com/forum/hall-f...-construct-return-last-match.html#post3304545
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998
Hope this helps.