Return last numeric value in a row

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

Example: Return last numeric value in a row

Return last numeric value in a row

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

EXCEL

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

METHOD 2. Return last numeric value in a row using the Excel HLOOKUP function

EXCEL

=HLOOKUP(9.99999999999999E+307,4:4,1)
The formula uses the Excel HLOOKUP function to return the last numeric value in row 4.

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

VBA

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

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 ("C6") in the VBA code to any cell in the worksheet.
Row Reference: Select the row that you are searching through for the last numeric value by changing the row reference ("4:4") in the VBA code to any row in the worksheet, that doesn't conflict with the formula.

METHOD 2. Return last numeric value in a row using the Excel HLOOKUP function

VBA

Sub Return_last_numeric_value_in_a_row()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'use the HLOOKUP function to return the last numeric value in row 4
ws.Range("C6").Value = Application.WorksheetFunction.HLookup(9.99999999999999E+307, ws.Range("4:4"), 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 ("C6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Row Reference: Select the row that you are searching through for the last numeric value by changing the row reference ("4:4") in the VBA code to any row in the worksheet, that doesn't conflict with the formula.

Return last numeric value in a row

EXPLANATION

EXPLANATION
To return the last numeric value in a row we can apply two methods using an Excel HLOOKUP function or a combination of an Excel INDEX and MATCH functions.
FORMULAS
=INDEX(row_ref,MATCH(9.99999999999999E+307,row_ref))
=VLOOKUP(9.99999999999999E+307,row_ref,1)
ARGUMENTS
row_ref: The row 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 row, given a numeric value existing in the selected row.