How to find the first occurrence of a desired item, searching from bottom to top in Excel

How to find the first occurrence of a desired item, searching from bottom to top in Excel

When working with large datasets in Excel, there are times when you need to find the first occurrence of a specific value, but starting the search from the bottom up. This technique is useful when your data is organized chronologically or in a way where the most recent entries are the most relevant. In this article, we’ll explore several methods to achieve this, including Excel formulas and a custom VBA function.

Methods to find the first occurrence from bottom to top

1. Using INDEX and MATCH

Formula:

  =INDEX($B$6:$B$15, MATCH(2, 1/($A$6:$A$15=$E$3))

This formula uses the INDEX and MATCH functions to find the last occurrence of the value in E3 in column A, then returns the corresponding value from column B.

2. Using XLOOKUP

Formula:

=XLOOKUP($E$3, $A$6:$A$15, $B$6:$B$15, , 0, -1)

XLOOKUP is a powerful function introduced in recent versions of Excel (Excel 2021, 2024, and 365). The -1 parameter specifies a bottom-to-top search.

3. Using LOOKUP

Formula:

=LOOKUP(2, 1/($A$6:$A$15=$E$3), $B$6:$B$15)

This formula uses LOOKUP to find the last occurrence of the value in E3 in column A.

4. Using INDEX, SUMPRODUCT, MAX, and ROW

Formula:

=INDEX($B$6:$B$15,
    SUMPRODUCT(--MAX(ROW($A$6:$A$15)*($A$6:$A$15=$E$3))-ROW($A$5))

This formula combines multiple functions to find the last occurrence of the value in E3.

---

Now also a variant of a user-defined VBA function.

For those who prefer a more flexible solution, you can create a custom VBA function:

Function LASTINSTANCE(cRange As Range, cCriteria As String, cColumn As Range)
    Dim ii As Long
    
    For ii = cRange.Cells.Count To 1 Step -1
    	If cCriteria = cRange.Cells(ii) Then
        	LASTINSTANCE = cColumn.Cells(ii)
            Exit Function
        End If
    Next ii
    
End Function

5. LASTINSTANCE (user function)

Formula:

=LASTINSTANCE($A$6:$A$15,$E$3,$B$6:$B$15)

This function iterates through the specified range from bottom to top and returns the first value that matches the criteria.

---

Conclusion

Finding the first occurrence of a value from bottom to top in Excel can be achieved through several methods, each with its own advantages. If you prefer formulas, XLOOKUP and INDEX/MATCH are excellent options. For those who want more flexibility, a custom VBA function might be the ideal solution.

The file can be downloaded by clicking on the image below or here:

• All articles on this site can be distributed with acknowledgement of the source, including a link to the article or site.
• The file preview is not correct upon download due to the preview generated by Google Drive.

Other topics on the same topic:
- Auxiliary menu for Microsoft Excel.