How to Find Last Occurrence of a Value in a Column in Excel

Finding the last occurrence of a specific value in a column is a common task in Excel, especially when working with logs, repeated entries, or dynamic lists. Whether you want to return the row number, cell reference, or the actual value, Excel offers multiple solutions ranging from formulas to VBA.

In this article, we’ll walk through all practical methods to find the last instance of a value in a column. We’ll use formulas like LOOKUP, XLOOKUP, INDEX, and also offer a VBA-based solution for automation. You’ll find this helpful whether you’re analyzing sales logs, timestamps, or tracking repeated entries in long datasets.

This method uses a smart trick by combining the LOOKUP function with CHOOSE and ROW to pinpoint the last row number where a value appears. It’s great when you need to extract positional information rather than the value itself.

For instance, your data is in column A and you’re searching for the last occurrence of “Apple“.

Steps:

➤ Select a blank cell such as E6.
➤ Use the following formula:

➤ Press Enter and you’ll get the row number of the last “Apple“.

This formula setup helps return the actual value corresponding to the last matching entry. It’s ideal when you’re dealing with parallel columns, like names and dates or products and prices.

Steps:

➤ Enter your target value in a cell, say E4 = “Banana“.
➤ Use this formula to find the last matched value in Column B (e.g., Price):

➤ Press  Ctrl  +  Shift  +  Enter  if you’re not on Excel 365.

You’ll get the corresponding value for the last “Banana“.

XLOOKUP makes things easier in modern Excel versions. You can reverse the search order and find the last occurrence of a value cleanly.

Steps:

➤ In Excel 365, select a blank cell such as E5.
➤ Enter this formula:

This searches for the last “Orange” in Column A and returns the value from Column B. It works even with blank cells and skips over them gracefully.

If you’re handling large datasets or want to automate the search, VBA is a great choice. This method returns the row number of the last match instantly.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ Insert a new module and paste the code below:

Function LastRowOfValue(colRange As Range, targetVal As Variant) As Long
    Dim i As Long
    For i = colRange.Rows.Count To 1 Step -1
        If colRange.Cells(i, 1).Value = targetVal Then
            LastRowOfValue = colRange.Cells(i, 1).Row
            Exit Function
        End If
    Next i
    LastRowOfValue = 0
End Function

➤ Use it in Excel like this:

It returns the last row number where “Apple” appears.

Frequently Asked Questions

How do I get the row number of the last occurrence of a value?

Use this formula: =LOOKUP(2,1/(A2:A12=”Value”),ROW(A2:A12)). It returns the row number by evaluating an array from bottom to top.

Can XLOOKUP find the last value in Excel 365?

Yes. XLOOKUP supports reverse searching with its last argument -1. For example: =XLOOKUP(“Apple”,A2:A12,B2:B12,,0,-1) returns the last match for “Apple“.

Will blank rows or empty cells affect these formulas?

No. Most formulas skip over blanks as long as your criteria are well-defined. However, ensure your range doesn’t include too many irrelevant blanks at the bottom.

Wrapping Up

In this tutorial, you’ve learned multiple ways to find the last occurrence of a value in a column ranging from simple formulas to VBA macros. Whether you’re tracking product entries, latest dates, or specific keywords, these methods work reliably for all skill levels. Feel free to download the practice file and share your thoughts and suggestions.