Ron - Thank you, that does solve my problem.

I don't think there's any way to determine this from Excel help. The
sentence you quote indicates that the function will match the LARGEST value
that's less than or equal to the lookup value. It does not say that it will
match the LAST value. It also does says that the list must be sorted in
ascended order, and does not define what happens when the list is not sorted.

But in any case, it's understandable that the function would work this way,
even though it's undefined. It's a clever solution.

Regards,
Ora

"Ron Coderre" wrote:

Regarding your questions about the LOOKUP function:

Per Exce help:
"If LOOKUP can't find the lookup_value, it matches the largest value in
lookup_vector that is less than or equal to lookup_value."

Using that information:
=LOOKUP(2,1/(A1:A100<""),A1:A100)
In that formula, the second argument divides the number 1 by 1 or 0, the
numeric result of whether each cell is non-blank (1) or blank (0). 1/1
returns 1. 1/0 returns #DIV/0!. Consequently, when LOOKUP cannnot locate 2 in
the second array, it will return the last numeric item in the list that is
less than 2, which is the last 1. That item will refer to the last non-blank
cell in the third argument.

=LOOKUP(10^99,A:A)
The same rules apply in that formula. 10^99 is a much larger number than I
would ever expect in a worksheet. When the LOOKUP function cannot find it in
a list of numbers, it will return the last number in the list that is less
than 10^99.
Note: If you happen to be an astronomer or scientist, you may want to lookup
the largest number Excel can handle: 9.99999999999999E-307 instead of 10^99


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Eric" wrote:

Ron - Thank you. Yes, your suggestion does solve my problem. But honestly,
it's a bit of a mystery why. The help text for LOOKUP says that the values
have to be in ascending order... but mine are not. So I don't know how it
works.

As to your second formula, it's even more mysterious. Why the 2 as the
first parameter? Also, I don't understand the syntax. What does the
"1/(A1:A100<"")" mean? The help text indicates that the second parameter
must be a "lookup vector" which contains only one row or column. I don't
understand Excel's formula syntax. What does Excel mean by 1 slash an
expression containing a range? What am I missing?

I'm not a novice at this stuff, but I don't understand why this works.

Thanks for your help, and any additional explanation you can offer.

Eric

"Ron Coderre" wrote:

Try one of these:

Last numeric cell in Col_A:
=LOOKUP(10^99,A:A)

Last non-blank cell in Col_A:
=LOOKUP(2,1/(A1:A100<""),A1:A100)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Eric" wrote:

I'm looking for a function or formula in Excel 2003 which will return the
last non-blank cell in a range. The values in the range are numeric;
however, they're not sorted in numeric order. I could probably figure out
how to write a macro, but I'd prefer a formula. Any suggestions?

Thanks.