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.
Posts
11 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))))
Post a Comment