Get final non-blank cell in range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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?
 
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
 
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
 
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
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to find additional ranges outside of min, max, and average 2
row number of last non-blank cell 14
Search for specific text within last non blank cell in a range 2
First Non-Blank Cell 6
Find the lowest4 numbers of the last 6 in a range 8
VBA set thick borders around non empty cells in range 0
Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1
Finding location (or start cell) of highest consecutive values 3

Back
Top