Sandy Mann wrote:
Aladin,

I accept that your use of LOOKUP is more efficient than mine, (well my
plagiarised version really, probably the only original thought I have had
was, "I wonder if there is anything on the net about Excel?"), for one thing
'mine' used two function calls. So that I can expand my understanding of
how XL works, do you know if LOOKUP checks all 65,536 rows or just the used
range? If it does check all rows, is it still faster than a limited range -
say 1,000 rows. I other words is it 65 times faster?


LOOKUP() effects a binary search (see:
http://www.nist.gov/dads/HTML/binarySearch.html), thus very fast.

In

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

it's not LOOKUP itself that requires time. Rather:

(a) the evaluation of ISNUMBER(L1:L1000), and

(b) 1/Expression

Hence, the formula

=LOOKUP(2,1/ISNUMBER(L1:L1000),L1:L1000)

is significantly less efficient compared to

=LOOKUP(9.99999999999999E+307,L:L)

When the interest is in retrieving the last numeric value, one would
should invoke the latter, not the former.