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.