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,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


"Ragdyer" wrote in message
...
This will display the value in the last non-blank cell in Row2, *either*
Text or Number:

=LOOKUP(2,1/(2:2<""),2:2)


--
HTH,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-

"Koffiepit" wrote in message
...
Does anybody know how to construct a worksheet function that displays
the
location, or better still, the value, of the last non-blank cell in a
large
range like the second row in a spreadsheet for instance?

O yes, and if you do know, could you explain how it works?

Much appreciated in advance.

Koffiepit