How to Find the Last Row with a Specific Value in Excel (6 Methods)

Here, we have a sample dataset containing Customer Name, Gender, Loan Purpose, Job, and Credit Risk. The dataset is repetitive. Snoop, Almanda, and Catherine have data more than once. We want to know their last Credit Risk data.

Method 1 – Finding the Last Row with a Specific Value Using the MAX Function

Steps:

Enter the following Formula in cell I5:

=MAX(IF($B$5:$B$15=H5,ROW($B$5:$B$15)))

Press the CTRL+SHIFT+ ENTER key. ( If you are using Excel updated version from 2019, only pressing ENTER key will do).

Drag down to AutoFill rest of the series.

Formula Breakdown

IF($B$5:$B$15=H5,ROW($B$5:$B$15)) yields the result as {5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;15}. Where 5 and 15 indicate the Row number for Catherine.
The MAX Function will take 15 as the maximum number and give us the result.

Method 2 – Using the SUMPRODUCT Function to Find the Last Row with a Specific Value

Steps:

Enter the following formula in cell I5:

=SUMPRODUCT(MAX(ROW(B5:F15)*(B5:B15=H5)))

Press ENTER or CTRL+SHIFT+ENTER

Drag down to AutoFill rest of the series.

Formula Breakdown

ROW(B5:F15)*(B5:B15=H5) gives us the output as {5;0;0;0;0;0;0;0;0;0;15}
SUMPRODUCT(MAX{5;0;0;0;0;0;0;0;0;0;15}) will give the final output as 15.

Method 3 – Finding the Last Row with a Specific Value by the INDEX Function

Steps:

Enter the following formula in cell I5:

=INDEX($F$5:$F$15,SUMPRODUCT(MAX(ROW($B$5:$B$15)*(H5=$B$5:$B$15))-4))

Press ENTER.

Drag down to AutoFill rest of the series.

Here, the function SUMPRODUCT(MAX(ROW($B$5:$B$15)*(H5=$B$5:$B$15))-4 gives us the result 11. 11 indicates the Row number in the table. As you can see, the last row in the worksheet is 15, but the data in our table started from Row 5, so we use -4 in the formula.
Then, the formula =INDEX($F$5:$F$15,11) will yield a Very High output.

Method 4 – Finding the Last Row with a Specific Value in Excel Using the LOOKUP Function

Steps:

Enter the following formula in cell I5:

=LOOKUP(2,1/($B$5:$B$15=H5),$F$5:$F$15)

Press ENTER.

Drag down to AutoFill rest of the series.

Method 5 – Finding the Last Row with a Specific Value by the MATCH Function

Steps:

Enter the following formula in cell I5:

Press ENTER or CTRL+SHIFT+ENTER

Drag down to AutoFill the rest of the series.

Method 6 – Finding the Last Row with a Specific Value by VBA

Steps:

Right-click on the sheet and go to View Code.

Enter the VBA code below.

VBA Code:

Option Explicit
Function MylastrowFunction(SpecificValue As String, SpecificRange As Range, LastColumnNumber As Integer)
Dim i As Long
For i = SpecificRange.Columns(1).Cells.Count To 1 Step -1
If SpecificValue = SpecificRange.Cells(i, 1) Then
MylastrowFunction = SpecificRange.Cells(i, LastColumnNumber)
Exit Function
End If
Next i
End Function