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.

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.

Find Last Occurrence of a Value in a Column in Excel


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

Find Last Occurrence of a Value in a Column in Excel

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

Find Last Occurrence of a Value in a Column in Excel

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.

Find Last Occurrence of a Value in a Column in Excel

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.

Find Last Occurrence of a Value in a Column in Excel

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.

Find Last Occurrence of a Value in a Column in Excel

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.

Find Last Occurrence of a Value in a Column in Excel

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

Find Last Occurrence of a Value in a Column in Excel

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

Find Last Occurrence of a Value in a Column in Excel

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

Find Last Occurrence of a Value in a Column in Excel

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.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

2 Comments
  1. 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 the formula bar.
      =INDEX(D:D,LARGE(IF(D:D<>"",ROW(D:D)),2))
      3. Press Shift + Ctrl + Enter.

      Note: You have to press Shift + Ctrl + Enter together, 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
      .

Leave a reply

ExcelDemy
Logo