If you want to **Find Last Occurrence of a Value in a Column in Excel**, this article is for you. Here, we will walk you through 5 easy and handy methods, so that you can find the last occurrence of a value in a Column In Excel.

**Table of Contents**hide

## Download Workbook

## 5 Methods to Find Last Occurrence of a Value in a Column in Excel

Here, the following **Product List** table shows **Month**, **Item**, and** Price** of different products. From the table, we can see that an item has different prices in different months, and we will determine the last occurrence price of any product using 5 different methods. Here, we use Excel 365. You can use any available Excel version.

### Method-1: XLOOKUP Function to Find Last Occurrence of a Value in a Column

We want to find the** Last Occurrence Price** of Egg in cell **G4 **from our** Product List** table using the **XLOOKUP function**.

➤ To begin with, we will type the following formula in our cell **G4**.

`=XLOOKUP(G3,C4:C11,D4:D11,,,-1)`

➤ Here, we give** lookup_value** as **G3**, **lookup_ array** as **C4:C11**, and **return_array** as **D4:D11**.

➤ We type “**,,,-1**” to search from last to first. Here, we’ve skipped several placeholders as we didn’t need those.

➤ Then, press **Enter**.

➤ Finally, we can see the **Last Occurrence Price **of the Egg is $180, and the formula is in the **Formula Bar**.

**Read More:** **How to Find Value in Column in Excel (4 Methods)**

### Method-2: LOOKUP Function to Find Last Occurrence of a Value

In this method, we want to find the** Last Occurrence Price **of Egg using the **LOOKUP function**.

➤ First of all, we have to type the following formula in cell **G4**, and press **Enter**.

`=LOOKUP(2,1/($C$4:$C$11=$G$3),$D$4:$D$11)`

This is how the formula works:

- The lookup value is 2 because the lookup function will find 2 through the column, when it reaches an error then it will return back to its nearest value 1 and will show that result. The result will be $180.
- The lookup range is
**1/($C$4:$C$11=$G$3****)**– returns 1 if it finds any match and an error if no match is found. So, for the lookup value Egg, the array would be {**#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1#DIV/0!**}. - The third parameter,
**$D$4:$D$11**, is the range from which it’ll return the result, which are prices here.

➤ Finally, we can see the **Last Occurrence Price** of Egg is $180, and we can see the formula in the **Formula Bar**.

**Read More:** **Lookup Value in Column and Return Value of Another Column in Excel**

### Method-3: Using INDEX and MATCH Functions

Here, we want to determine the *Last Occurrence Price* of the Egg using the **INDEX** and** MATCH **functions.

➤ First of all, we will type the following formula in cell **G4**.

`=INDEX($D$4:$D$11,MATCH(2,1/(C4:C11=G3)))`

In the formula,

**$D$4:$D$11**is the column range that contains the value we will return**C4:C11**is the column range we are looking for**G4**contains the criteria you will do the search based on.

➤ Then, press** Enter. **

➤ Finally, we can see the **Last Occurrence Price** of Egg is $180.

**Read More:** **How to Find Top 5 Values and Names in Excel (8 Useful Ways)**

### Method-4: Combination of MAX, IF, ROW, and INDEX Functions

Here, we want to find the** Last Occurrence Position** of Egg in cell **G4**. We will use a combination of **MAX**, **IF**, **ROW**, and **INDEX **functions.

➤ To begin with, we will type the following formula in cell **G4**.

`=MAX(IF(C4:C11="Egg", ROW(C4:C11)-ROW(INDEX(C4:C11,1,1))+1))`

In this formula,

**=ROW(C3:C7)-ROW(INDEX(C4:C11,1,1))+1**⟶ provides the relative position of the rows within the range** C4:C11.** You will find an array as the result.

**=IF(C4:C11=”Egg”, ROW(C4:C11)-ROW(INDEX(C4:C11,1,1))+1)****⟶** **IF** will check whether every value within the range **C4:C11** is equal to “Egg”, returns the relative position when **TRUE**, otherwise, returns **FALSE**.

**=MAX(IF(C4:C11=”Egg”, ROW(C4:C11)-ROW(INDEX(C4:C11,1,1))+1))****⟶** the **MAX **function will test the array result we find from the **IF **function and provides the highest value from the resultant array. So, the last occurrence of text string “Egg” in range **C4:C11** will be returned. It is 7.

➤ Now, press **Enter**.

➤ Finally, we can see the **Last Occurrence Position** of the **Item** Egg in cell **G4**.

**Read More:** **How to Find First Occurrence of a Value in a Column in Excel (5 Ways)**

### Method-5: Find Last Occurrence of a Value in a Column Using VBA Code

In this method, we will use** VBA **code to find the last occurrence of a value in a column in Excel.

➤ To begin with, we will copy the unique items from the main sheet to a new sheet. Here he put the unique items in **Sheet6**.

➤ After that, we will go to the main sheet and click on any cell. Here, we click on cell** F4**.

➤ Now, we will go to the **Data** tab in the **Ribbon**

➤ We will click on **Data Tools**

➤ Then, we will select **Data Validation**.

➤ A **Data Validation** window will appear.

➤ We will select** List** from the **Allow** bar.

➤ After that, we have to give a source in the** Source** box.

➤ Now, we select the unique items from **Sheet6** as our **Source**.

➤ After that, Click** OK**.

➤ We can see a **down arrow** sign is shown on the right side of our selected cell **F4**. By clicking here, we can select any item name. This will save our time because we won’t need to type the items every time.

➤ Now, we’ll make a new function named **LastOccur **with Excel** VBA.**

➤ To do so, first, we will right-click on our sheet name.

➤ Then, we will select **View Code** from the context menu.

➤ A** VBA **window will appear.

➤ Now, we will click on** the Insert** tab and select **Module**.

➤ Here, a new **Module 3** appears along with a window.

➤ We will type the following code in that window.

```
Function LastOccur(LValue As String, LRange As Range, ColNum As Integer)
Dim l As Long
For l = LRange.Columns(1).Cells.Count To 1 Step -1
If LValue = LRange.Cells(l, 1) Then
LastOccur = LRange.Cells(l, ColNum)
Exit Function
End If
Next l
End Function
```

➤ Now, we will go to our active sheet and we will type the following formula in cell **G4**.

`=lastOccur(F4,C4:D11,2)`

➤ Finally, we can see the **Last Occurrence Price** of **Bread **in cell **G4**.

➤ We can see the last occurrence price of any item by clicking the down arrow situated right side of cell **F4**.

**Read More:** **How to Find Value in Column Using VBA in Excel (4 Ways)**

## Conclusion

Here, we tried to show you some easy methods that will help you to Find the Last Occurrence of a Value in a Column in Excel. We hope you will find this article helpful. If you have any queries or suggestions, please feel free to know us in the comment section.

This VBA formula works like a charm for me. Thank you very much!

I could solve my issue with some array functions, but since my data set is over 60000 rows and 30+ columns, Excel isn’t happy with my array functions.

Instead, if I wanted to include some criteria e.g.,

if column C is 0, go to second last result, OR if column D is “text value”, go to second last result.

Hello, Niki!

Thanks for sharing your problem with us!

You can use the formula to find the second last result from a certain cell.

For this,

1.Select the cell where you want to see the result.2.Insert the formula into theformula bar.`=INDEX(D:D,LARGE(IF(D:D<>"",ROW(D:D)),2))`

3.PressShift+Ctrl+Enter.Note:You have to pressShift+Ctrl+Entertogether, otherwise the formula won’t work.Can you please send me your dataset at ([email protected]), so that I can help you?

Hope this will help you!

Good Luck!

Regards,

Sabrina Ayon.Author, ExcelDemy