Find Last Occurrence of a Value in a Column in Excel

by

Sometimes, you may want to find the last occurrence of a value in a column in Excel.

For example, If you intend to highlight or format the cell containing the last occurrence of a value in a column, knowing the location of that cell is necessary.

This tutorial shows four techniques for finding the last occurrence of a value in a column in Excel.

Method #1: Use the XLOOKUP Function to Find the Last Occurrence of a Value in a Column in Excel

We can apply the XLOOKUP function with a negative search mode to locate the last occurrence of a value in a column in Excel.

The XLOOKUP function searches an array or a range for a match and returns the corresponding item from a second array or range.

Note: The XLOOKUP function is only available in Excel 365. In case you are using an older version of Excel you can use the other methods covered in this article.

Let’s consider the following dataset of a list of transactions:

Data set to find the last occurrence of a value in a column

We want to use the XLOOKUP function to find the last transaction ID in column A for the lookup value in cell F2.

We use the following steps:

  1. Select the cell F3 and type in the formula below:
=XLOOKUP(F2,B2:B15,A2:A15,,,-1)
XLOOKUP formula to find the last occurrence of a value
  1. Press Enter.

The formula returns the value 266, the last transaction ID in column A for the lookup item in cell F2 (Smartphone).

Result of the Xlookup formula

In case you want to get the price of the last item instead of the Transaction ID, you can use C2:C15 as the third argument of the formula (instead of A2:A15)

Explanation of the Formula

=XLOOKUP(F2,B2:B15,A2:A15,,,-1)

The formula does a reverse search starting at the last item in the range B2:B15 for the lookup value in cell F2.

This is done by specifying -1 as the last argument (which is the search mode argument, and -1 will make the function start the search from the last item)

If an exact match is found, it returns the corresponding value from the cell range A2:A15. If an exact match is not found, it returns #N/A.

Also read: Check If Value is in List in Excel

Method #2: Use an INDEX Formula to Find the Last Occurrence of a Value in a Column in Excel

We can use a formula combining the INDEX, MAX, IF, ROW, and MIN Functions to find the last occurrence of a value in a column in Excel.

Assume we have the following dataset of transactions:

Dataset to find the last occurrence of the item

We want to use an INDEX  formula to find the last price of the lookup value in cell F2 in column C.

We use the below steps:

  1. Select cell F3 and type in the following formula:
=INDEX(C2:C15, MAX(IF(B2:B15=F2, ROW(B2:B15)-MIN(ROW(B2:B15))+1, 0)))
Index Formula to find the last occurrence of the item
  1. Press Enter.
Result of the index formula

The formula returns 3000, the most recent price in column C of the lookup item in cell F2 (Laptop).

Explanation of the Formula

=INDEX(C2:C15, MAX(IF(B2:B15=F2, ROW(B2:B15)-MIN(ROW(B2:B15))+1, 0)))

This formula searches for the last occurrence of a value (F2) in column B (B2:B15) and returns the corresponding value from column C (C2:C15).

Let’s break down the formula step by step:

  1. B2:B15=F2: This part of the formula checks if the values in the cell range B2:B15 are equal to the value in cell F2. It returns an array of TRUE and FALSE values, where TRUE indicates a match and FALSE indicates no match.
  2. ROW(B2:B15)-MIN(ROW(B2:B15))+1: This part of the formula calculates the relative row numbers of the values in the cell range B2:B15. It subtracts the minimum row number from each row number and adds 1 to adjust the range to start from 1 instead of 0.
  3. IF(B2:B15=F2, ROW(B2:B15)-MIN(ROW(B2:B15))+1, 0): This part combines steps 1 and step 2 using the IF function. If a value in the cell range B2:B15 matches the value in cell F2, it returns the corresponding relative row number from step 2; otherwise, it returns 0.
  4. MAX(IF(B2:B15=F2, ROW(B2:B15)-MIN(ROW(B2:B15))+1, 0)): This part of the formula takes the maximum value from the array generated in step 3. It finds the highest relative row number where a match occurred.
  5. INDEX(C2:C15, MAX(IF(B2:B15=F2, ROW(B2:B15)-MIN(ROW(B2:B15))+1, 0))): Finally, the INDEX function returns the value from the cell range C2:C15 at the row number determined by the maximum value from step 4. 
Also read: How to Count Unique Values in Excel (Formulas)

Method #3: Use the Find and Replace Feature to Find the Last Occurrence of a Value in a Column in Excel

We can use the Find and Replace feature in Excel to find the last occurrence of a value in a column.

Suppose we have the following dataset of transactions:

Data set to find last occurrence of given value in column

We want to use the Find and Replace feature to find the last occurrence of the “Gaming Console” item in column B.

We use the following steps:

  1. Select the cell range B2:B15 where we want to search for the value.
Select the column
  1. On the Home tab, open the Find & Select drop-down on the Editing group and click Find:
Click on the find option in the find and select dropdown

Alternatively, you can press the shortcut Ctrl + F.

  1. On the Find and Replace dialog box, enter the item “Gaming Console” on the Find what field.
Enter the item that you want to find in the find what field
  1. Click on the Options button to expand the dialog box.
Click on the options button
  1. Open the Look in drop-down and select Values, select the Match entire cell contents option to search for an exact match, and click Find All.
Select the match entire cell contents option
  1. Press Ctrl + A to select all the cell references of the found cells at the bottom of the dialog box. The selection is highlighted in blue:
List of all the values found by find and replace
  1. Click Close to dismiss the dialog box.

The last occurrence of the item “Gaming Console” will be at the bottom of the selection in the column B:

Last occurrence would be at the bottom of the selection

One obvious drawback of this method is that it would only give you the last item in the selected column (unlike the formula methods covered above, which can also give you the Transaction ID or the Price of the last occurrence of the item you are looking for)

Also read: Find the Closest Match in Excel (Nearest Value)

Method #4: Use a User Defined Function to Find The Last Occurrence of a Value in a Column in Excel

We can use a user-defined function created in Excel VBA to find the last occurrence of a value in a column in Excel.

Assume we have the following dataset of transactions:

Data set to find last occurrence of a given item

We want to use a user-defined function created in Excel VBA to find the last price of the item in cell F2.

We use the following steps:

  1. Press the shortcut Alt + F11 to open the Visual Basic Editor.
  2. Open the Insert menu and choose the Module option to insert a module.
Click on insert and then click on module
  1. Copy the following function procedure and paste it into the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com

Function FINDLASTVALUE(LkUpValue As String, LkUpRange As Range, ColNumber As Integer)

    ' Declare a long integer variable 'i' for iteration.
    Dim i As Long

    ' Use a For loop to iterate over each cell in the first column of LkUpRange, from last to first (reversed order).
    For i = LkUpRange.Columns(1).Cells.Count To 1 Step -1

        ' Check if the LkUpValue is equal to the value in the current cell (i, 1).
        If LkUpValue = LkUpRange.Cells(i, 1) Then

            ' If LkUpValue is found, return the value from the cell in the same row but from the column specified by 'ColNumber'.
            FINDLASTVALUE = LkUpRange.Cells(i, ColNumber)

            ' End the function after finding the last occurrence.
            Exit Function
        End If

    ' End of the loop.
    Next i

' End of the function.
End Function
  1. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
  2. Press the shortcut Alt + F11 to switch to the active worksheet.
  3. Select cell F3 and type in the following formula:
=FINDLASTVALUE(F2,B2:C15,2)
Enter the custom function in Cell F3
  1. Press Enter.

The formula returns 2200, the last price in column C of the lookup item in cell F2 (Smart TV):

Result of the custom function

Explanation of the User-Defined Function

  1. The FINDLASTVALUE function takes three parameters: LkUpValue, LkUpRange, and ColNumber:
    • LkUpValue (As String): This parameter represents the lookup value we want to find in the specified range.
    • LkUpRange (As Range): This parameter represents the range of cells we want to search for the lookup value.
    • ColNumber (As Integer): This parameter specifies the column number from which we want to retrieve the last matching value.
  2. The function uses a loop to iterate through the cells in the specified range, starting from the last cell and moving toward the first cell (Step -1). It compares each cell value in the first column of the range (LkUpRange.Cells(i, 1)) with the lookup value (LkUpValue).
  3. If a match is found, the function assigns the corresponding value from the specified column (ColNumber) in the same row to the function name (FINDLASTVALUE). It then exits the loop and returns the found value.

This tutorial showed four techniques for finding the last appearance of a value in a column in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment