Tuesday, April 28, 2009

Return the Last Numeric Value in a Column

Returning the final value in a column is particularly useful for spreadsheets that involve running balances, where you want to reference the final balance regardless of whether it is the smallest or largest value in the column.


After trying a variety of different formulas for returning the last numeric value in a column, I settled on the following two.

=VLOOKUP(9.99999999999999E+307,A:A,1)
   or
=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))


Both of these formulas work very well and are still able to return the last numeric value even if the range contains blanks, error values (like #N/A), and text values.

The value 9.99999999999999E+307 is the largest number you can enter in a cell. The range doesn't have to be a full column. You could use B5:B100 instead of A:A.

21 comments :

JRD said...

Is there a formula for finding the first numeric value in a column? My column contains blanks, error values and text. Changing the 9.9E307 to 0 or 1 does not seem to work, especially with text in the first cell. Many Thanks.

assortZ said...

Hi

my 3hrs search today finally came to an end when i landed at your formula..thanks for the article :)

Anonymous said...

The VLOOKUP is simple and brilliant

Anonymous said...

Many thanks from Belgium. I have been looking for this for a looong time!

Anonymous said...

Perfect. Exactly what I needed. THANKS!

Anonymous said...

works like a dream provided all cells after final figure are empty, not 0.

Tim said...

Works in LibreOffice too. Thanks.

tambo said...

The VLOOKUP method shown here is probably the most reliable method I've found. I've seen all manner of messy INDEX/MATCH systemsm along with array formulas, but this handles errors and blanks better than all.

Can also be adapted for HLOOKUP easily enough.

Thanks for posting. I've learnt something new about the versatility of VLOOKUP.

jnf said...

hi does anyone know the formula to ignore 0 if its the last entry
thanks john

Anonymous said...

AWESOME!! Thank you! :-)

dreb said...

=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))

Anonymous said...

Hi! Yes, how to ignore 0 if its the last entry?

Anonymous said...

I was very happy to find your post. I have been scratching my head for a very long time trying to figure out how to do this. From Chicago land area very much appreciated and Thank You.

Cyril said...

jfn to return last integer but ignore zero (0) use CSE =INDEX(A:A,MAX((A:A<>0)*(ROW(A:A))))

vijay,india said...

simple formula but wonderful.

Anonymous said...

Amazing formula. Only issue was the range to reference could not be in a different worksheet of the file. The formula only worked within the same worksheet. Otherwise, a #value error occurred.

Anonymous said...

The formula worked well within a specific worksheet of a file but returned a "#value" message when referring to a reference range (column) in a different worksheet of the file. Is there any way around that?

Anonymous said...

This information saved so much time and effort. Thanks for the help.

One question-when the range of the array cells references a different worksheet, and the last cells of the column are blank, the formula returns a zero rather than the last number contained several cells above.
Any easy way around this other than to reference the specific last entered cell of column range?

Anonymous said...

This is excellent and very helpful. As everyone mentioned, simple yet effective formula... this is what I call thinking 'out of the box'. Thank you VERY much for posting.

Ben C. said...

Thanks so much for this solution. Works brilliantly!

Anonymous said...

Thank you so much for posting this. I have been looking for a while for this solution