Hi Raqdyer,
Just jumping in, thanks for the function, and the explanation. Understanding
is always better than just using.
Regards - Dave.

"Ragdyer" wrote:

If you would like to see how it works, change the formula to a smaller range
so that the formula evaluation will display - without a "Too Large" error.

Say 10 cells:
=LOOKUP(2,1/(A2:J2<""),A2:J2)

Enter this formula in A1, and put a value in any 2 cells within the range.

Now, in the formula bar, select *only*:
(A2:J2<"")
And hit <F9

You see an array of True and False, where the populated cells return True.

Hit <Esc to revert back to the formula without destroying it.

Trues evaluate to 1's and Falses to 0's.

So, now select in the formula bar *only*:
1/(A2:J2<"")
And hit <F9

You now see an array of 1's and #DIV/0! errors.
This comes from dividing 1 by 1 and 1 by 0.
Hit <Esc

In this form of the Lookup function, the one dimension lookup vector is
between the first and second commas.
This means the lookup vector is *NOT* A2:J2,
But *IS* the array of 1's and #DIV/0! errors.

The lookup value in this formula is 2, but there is *no* 2 in the lookup
vector.

NOW, follow this anomaly:
The Lookup() function pre-supposes that the lookup vector is sorted,
ascending.
If it can't find the lookup value, it's programmed to find the largest value
in the lookup vector that is *less* than or equal to the lookup value.
It by-passes the errors, and since it believes that the lookup vector is
sorted ascending, it returns the *last* 1, which, if sorted, *should* be the
largest value that's less then the lookup value.

So here, the lookup value of 2 cannot exist and is *never* found.

This anomaly can also work if you're just looking for the last numerical
value in a range, or just the last text value in a range.

For numbers, make the lookup value larger then any number that may possibly
exist in the lookup vector:
=LOOKUP(99^99,2:2)

And the same concept for text:
=LOOKUP(REPT("z",255),2:2)

--
Regards,