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

Get FREE Advanced Excel Exercises with Solutions!

Here, we have a sample dataset containing Customer Name, Gender, Loan Purpose, Job, and Credit Risk. As we can see, there is repetition in the dataset. Snoop, Almanda, and Catherine have data more than one time, so we want to know their last data for Credit Risk. We will see several methods for Excel Find Last Row with a Specific Value in this article.

Last Row with Specific Value in excel


How to Find Last Row with a Specific Value in Excel: 6 Easy Methods

Throughout this post, we will see the use of MAX, INDEX, SUMPRODUCT, LOOKUP, MATCH function, and a VBA code to do our task easier.


Method 1:  Find Last Row with a Specific Value Using MAX Function

Using the MAX function we can easily determine the last row number for a specific value.

Steps:

  • First, type the following Formula in cell I5.
=MAX(IF($B$5:$B$15=H5,ROW($B$5:$B$15)))

Last Row with Specific Value in excel Max function

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

  • Finally, drag down to AutoFill rest of the series.

Last Row with Specific Value in excel Max function

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.
  • And finally, MAX Function will take 15 as the maximum number and give us the result.

Read More: How to Use Excel Formula to Find Last Row Number with Data


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

To find the last row with a specific value, SUMPRODUCT is another function we can use.

Steps:

  • First, type the following formula in cell I5.
=SUMPRODUCT(MAX(ROW(B5:F15)*(B5:B15=H5)))

  • After that, press ENTER key or CTRL+SHIFT+ENTER in case you are using Excel 2019 or below of that version.

Last Row with Specific Value sumproduct function

  • Now, 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:  Find Last Row with a Specific Value by INDEX Function

Till now, we have seen the use of functions to get row numbers. This method will give us the exact value in the last row.

Steps:

  • First, we have to type the following formula in cell I5.
=INDEX($F$5:$F$15,SUMPRODUCT(MAX(ROW($B$5:$B$15)*(H5=$B$5:$B$15))-4))

Last Row with Specific Value INDEX

  • Now, press the ENTER key.

Last Row with Specific Value Combination Function

  • Finally, 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, in the worksheet the last Row is 15, but data in our table started from Row 5 that’s why we use -4 in the formula.
Then, formula =INDEX($F$5:$F$15,11) will yield the output Very High.


Method 4: Find Last Row with a Specific Value in Excel Using LOOKUP Function

In this method, we will see the use of the LOOKUP function to get the last row with a specific value.

Steps:

  • First, type the following formula in cell I5.
=LOOKUP(2,1/($B$5:$B$15=H5),$F$5:$F$15)

Last Row with Specific Value Using LOOKUP

  • Now, press the ENTER key.

  • Finally, drag down to AutoFill rest of the series.

That’s it.


Method 5: Find Last Row with a Specific Value by MATCH Function

This is kind of similar method, we used in Method 3.

Steps:

  • First, type the following formula in cell I5.

Last Row with Specific Value Combination Function

  • After that, press ENTER key or CTRL+SHIFT+ENTER in case you are using Excel 2019 or below of that version.

  • Finally, drag down to AutoFill the rest of the series.


Method 6:  Find Last Row with a Specific Value by VBA

In our last method, we will see a VBA code.

Steps:

  • First, right-click on the sheet and go to View Code.

Last Row with Specific Value VBA

  • After that, copy and paste 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

Last Row with Specific Value vba

  • After that, press the F5 or play button to run the code.
  • Now, type the following formula in cell I5.
=MylastrowFunction(H5,B5:F15,5)

  • After that, press ENTER key or CTRL+SHIFT+ENTER in case you are using Excel 2019 or below of that version.

Last Row with Specific Value by user function

  • At last, drag down to AutoFill the rest of the series.


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.

Last Row with Specific Value


Download Practice Workbook


Conclusion

These are 6 different methods for Excel to Find Last Row with a Specific Value. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles


<< Go Back to Find Value in Range | Excel Range | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo