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.

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

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 First Occurrence of a Value in a Column in Excel

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

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

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

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

**Download Workbook**

## 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 let us know in the comment section.

## Related Articles

- How to Find Last Non Blank Cell in Row in Excel
- How to Find Last Cell with Value in Column in Excel
- How to Find Last Cell with Value in a Row in Excel
- Find Last Value in Column Greater than Zero in Excel

**<< Go Back To Excel Last Value in Range | Excel Find Value in Range | Excel Range | Learn Excel**

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