How to Find Last Occurrence of a Value in a Column in Excel (5 Methods)

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.

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

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.

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.

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.

➤ 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