VB Vlookup which will find the last match

Question

• How do i create an VB Vlookup which will find the last match or last 3 match?
• lookup from bottom of my range B4:B9999

I have tried:

Private Sub FindRecord_Click()

Label21 = Application.WorksheetFunction.VLookup(ComboBox3.Value, Worksheets("Transactions").Range("B4:P9999"), 1, False)
Label21 = Application.WorksheetFunction.VLookup(ComboBox3.Value, Worksheets("Transactions").Range("B4:P9999"), 2, False)***

End Sub
Private Sub UserForm_Initialize()
ComboBox3.RowSource = "'[TEST46.xlsm]Transactions'!B4:B9999"**
End Sub

i have tried google around, those provided solution is rather for excel vlookup or not working.

Answer 1

This will return a reference to the last three found items.
You can then use OFFSET to return values from adjacent cells.
The code's a bit messy and can probably be improved, but it gives you the idea.

Public Sub Test()

    Dim MyRange As Range
    Dim rCell As Range
    
    'Look for the value 4 in second column of Sheet3.
    Set MyRange = Find_Last_Three(4, Sheet3.Columns(2))
    
    If Not MyRange Is Nothing Then
        For Each rCell In MyRange
            'Print the values from the 2 cells to the right of the found cells.
            Debug.Print rCell.Offset(, 1) & " : " & rCell.Offset(, 2)
        Next rCell
    End If

End Sub

Public Function Find_Last_Three(ValueToFind As Variant, RangeToLookAt As Range) As Range

    Dim rFound As Range
    Dim rReturnedRange As Range
    Dim sFirstAddress As String
    Dim x As Long

    With RangeToLookAt
        Set rFound = .Find(What:=ValueToFind, _
                           After:=.Cells(1, 1), _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           SearchDirection:=xlPrevious)
        If Not rFound Is Nothing Then
            Set rReturnedRange = rFound
            sFirstAddress = rFound.Address
            For x = 1 To 2
                Set rFound = .FindPrevious(rFound)
                If rFound.Address <> sFirstAddress Then
                    Set rReturnedRange = Union(rReturnedRange, rFound)
                End If
            Next x
        End If
    End With
    
    Set Find_Last_Three = rReturnedRange

End Function

EDIT:
To put the code into practice on a userform:

• Create a combobox called ComboBox3.
• Create a label called Label1. Make sure the Label is wide and tall enough to show all your data (three rows).

Add this code to the userform
(you must still have Find_Last_Three in a module. You can delete Test if you want):

Private Sub UserForm_Initialize()
    Me.ComboBox3.RowSource = "Transactions!B4:B9999"
End Sub

Private Sub ComboBox3_Change()
    Dim rLastThree As Range
    Dim rCell As Range
    
    Set rLastThree = Find_Last_Three(Me.ComboBox3.Value, Range(Me.ComboBox3.RowSource))
    
    If Not rLastThree Is Nothing Then
        Me.Label1.Caption = ""
        For Each rCell In rLastThree
            Me.Label1.Caption = Me.Label1.Caption & rCell.Offset(, 1) & " : " & rCell.Offset(, 2) & vbCr
        Next rCell
    End If
End Sub

Note: rCell.Offset(,1) and rcell.Offset(,2) is where it's getting the extra info from - offset from column B by 1 and 2 columns.

With my example data it returns this result showing the last three occurrences of H were on rows 11, 15 and 18: