Return last numeric value in a column

To return the last numeric value in a column we can apply two methods using an Excel VLOOKUP function or a combination of an Excel INDEX and MATCH functions

Example: Return last numeric value in a column

Return last numeric value in a column

METHOD 1. Return last numeric value in a column using the Excel INDEX and MATCH functions

EXCEL

=INDEX(B:B,MATCH(9.99999999999999E+307,B:B))
The formula uses the Excel INDEX and MATCH functions to return the last numeric value in column B.

METHOD 2. Return last numeric value in a column using the Excel VLOOKUP function

EXCEL

=VLOOKUP(9.99999999999999E+307,B:B,1)
The formula uses the Excel VLOOKUP function to return the last numeric value in column B.

METHOD 1. Return last numeric value in a column using the Excel INDEX and MATCH functions

VBA

Sub Return_last_numeric_value_in_a_column()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'use the INDEX and MATCH functions to return the last numeric value in column B
ws.Range("E4").Value = Application.WorksheetFunction.Index(ws.Range("B:B"), Application.WorksheetFunction.Match(9.99999999999999E+307, ws.Range("B:B")))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code to any cell in the worksheet.
Column Reference: Select the column that you are searching through for the last numeric value by changing the column reference ("B:B") in the VBA code to any column in the worksheet, that doesn't conflict with the formula.

METHOD 2. Return last numeric value in a column using the Excel VLOOKUP function

VBA

Sub Return_last_numeric_value_in_a_column()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'use the VLOOKUP function to return the last numeric value in column B
ws.Range("E4").Value = Application.WorksheetFunction.VLookup(9.99999999999999E+307, ws.Range("B:B"), 1)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code to any cell in the worksheet.
Column Reference: Select the column that you are searching through for the last numeric value by changing the column reference ("B:B") in the VBA code to any column in the worksheet, that doesn't conflict with the formula.

Explanation about the formula used to return last numeric value in a column

EXPLANATION

EXPLANATION
To return the last numeric value in a column we can apply two methods using an Excel VLOOKUP function or a combination of an Excel INDEX and MATCH functions.
FORMULAS
=INDEX(column_ref,MATCH(9.99999999999999E+307,column_ref))
=VLOOKUP(9.99999999999999E+307,column_ref,1)
ARGUMENTS
colum_ref: The column from which to lookup the last numeric value.

ADDITIONAL NOTES
Note 1: 9.99999999999999E+307 is the largest number that can be held in a cell.
Note 2: Both of the formulas will ignore any cells with an error and still return the last numeric value in a column, given a numeric value existing in the selected column.