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.

**Table of Contents**hide

## Download Practice Workbook

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

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)))`

- 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.

**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**indicates 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 (2 Ways)**

### 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.

- 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**.

**Read More:** **Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)**

### Method 3:Â Find Last Row with a Specific Value by INDEX Function

Till now, we saw 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))`

- Now, press the
**ENTER**key.

- 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**.

**Read More:** **Find the Last Cell With Value in Row in Excel (6 Methods)**

### 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)`

- Now, press the
**ENTER**key.

- Finally, drag down to
**AutoFill**rest of the series.

Thatâ€™s it.

**Read More:** **How to Find If Cell Contains Specific Text in Excel**

**Similar Readings**

**How to Find * Character Not as Wildcard in Excel (2 Methods)****Find External Links in Excel (6 Quick Methods)****Find from Right in Excel (6 Methods)****How to Find Links in Excel****How to Find Lowest 3 Values in Excel (5 Easy Methods)**

### 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**.

- 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.

Done.

**Read More:** **How to Find Last Non Blank Cell in Row in Excel (5 Methods)**

### 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**.

- 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
```

- 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.

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

**Read More:** **Excel FindÂ Last Occurrence of Character in String (6 Methods)**

## 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.

## 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**

**Excel Function: FIND vs SEARCH (A Comparative Analysis)****How to Find External Links in Conditional Formatting in Excel (2 Ways)****How to Find Max Value in Range with Excel Formula (5 Easy Methods)****FIND Function Not Working in Excel (4 Reasons with Solutions)****[Solved!] CTRL+F Not Working in Excel (5 Fixes)****[Fixed]: Canâ€™t Find Project or Library Error in Excel (3 Solutions)**