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 :
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.
Hi
my 3hrs search today finally came to an end when i landed at your formula..thanks for the article :)
The VLOOKUP is simple and brilliant
Many thanks from Belgium. I have been looking for this for a looong time!
Perfect. Exactly what I needed. THANKS!
works like a dream provided all cells after final figure are empty, not 0.
Works in LibreOffice too. Thanks.
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.
hi does anyone know the formula to ignore 0 if its the last entry
thanks john
AWESOME!! Thank you! :-)
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))
Hi! Yes, how to ignore 0 if its the last entry?
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.
jfn to return last integer but ignore zero (0) use CSE =INDEX(A:A,MAX((A:A<>0)*(ROW(A:A))))
simple formula but wonderful.
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.
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?
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?
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.
Thanks so much for this solution. Works brilliantly!
Thank you so much for posting this. I have been looking for a while for this solution
Post a Comment