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

➤Type the following formula in our cell **G4**.

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

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

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

➤Press **Enter**.

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

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

Find the** Last Occurrence Price **of Egg using the **LOOKUP **function.

➤ 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, it will return to its nearest value, 1, and 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. 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.

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

### Method 3 – Using INDEX and MATCH Functions

Determine the *Last Occurrence Price* of the Egg using the **INDEX** and** MATCH **functions.

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

➤ Press** Enter. **

➤ See the **Last Occurrence Price** of Egg is $180.

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

Find the** Last Occurrence Position** of Egg in cell **G4**. We will use a combination of **MAX**, **IF**, **ROW**, and **INDEX **functions.

➤ 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 a 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**, 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.

➤ Press **Enter**.

➤ 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

Use** VBA **code to find the last occurrence of a value in a column in Excel.

➤ Copy the unique items from the main sheet to a new sheet. Put the unique items in **Sheet6**.

➤ Go to the main sheet and click on any cell. Click on cell** F4**.

➤ Go to the **Data** tab in the **Ribbon**.

➤ Cick **Data Tools**.

➤ Select **Data Validation**.

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

➤ Select** List** from the **Allow** bar.

➤ Give a source in the** Source** box.

➤ Select the unique items from **Sheet6** as our **Source**.

➤ Click** OK**.

➤ We can see a **down arrow** sign is shown on the right side of our selected cell **F4**. Select any item name. Save our time because we won’t need to type the items every time.

➤ Make a new function named **LastOccur **with Excel** VBA.**

➤ We will right-click on our sheet name.

➤ We will select **View Code** from the context menu.

➤ A** VBA **window will appear.

➤ Click on** the Insert** tab and select **Module**.

➤ **Module 3** appears along with a window.

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

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

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

➤ The **Last Occurrence Price** of **Bread **in cell **G4**.

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

**Download Workbook**

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