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.
Formula #1
=VLOOKUP(9.99999999999999E+307,A:A,1)
Formula #2
=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.
Download Example File: Get Last Numeric Value.xls
Also Read