Get final non-blank cell in range

E

Eric

I've got a range where I'm including a bunch of values. I need a formula
which will return the last value non-blank in the range. Not the highest or
lowest, simply the last.

Thanks.
 
L

L. Howard Kittle

Hi Eric,

Just to add to Biff's solution, if you knew for absolute sure that the
largest number in the column of interest would never exceed 365, then you
could use

=LOOKUP(366,A:A)

HTH
Regards,
Howard
 
E

Eric

1. The help text for LOOKUP says the values have to be in order. But in my
case, they won't always be. Will it still work even if not in order?

2. What if the values are a mix of numbers and alpha values?
 
B

Biff

Based on this statement:
Not the highest or lowest, simply the last.
Click to expand...

I assumed you were talking about NUMBERS in which case the formula would
work whether the numbers were sorted or not.
2. What if the values are a mix of numbers and alpha values?
Click to expand...

If you want the last value, text or number (excludes formula blanks: ""):

=LOOKUP(2,1/(A1:A20<>""),A1:A20)

Biff
 
R

RagDyeR

Harlan might say:
"Unneeded function call."
--

Regards,

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

Or even:

=LOOKUP(MAX(A:A)+1,A:A)

Biff
 
R

RagDyeR

I'm prejudiced!
I think:

=LOOKUP(99^99,A:A)

Looks neat!<bg>

--

Regards,

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

Biff said:
Harlan might say:
"Unneeded function call."
Click to expand...

Or Aladin!

Yeah, but if the range is "small" it's a lot less confusing than
9.99999999999999E+307 or 99^99.

Biff
 
Top