Returning the larger value on multiple matches in a VLookup within VBA

Within Excel VBA I wish to retrieve the maximum date found when multiple matches are found.   I tried the VBA Function Vlookup and a sort, but it does not work,,,

Sheet1
Lookup Value         Date (Expected returned Value)
Conoco                     01/26/1965
Amoco                     01/26/2005

Sheet2
Table Array
Conoco  01/01/1900
Conoco  01/26/1965
Shell       07/01/1995
Shell      01/26/2005
upobDaPlayaAsked:
Who is Participating?
 
Ejgil HedegaardConnect With a Mentor Commented:
Here is a VBA function that loops the range and find the maximum date for the type.
Function MaxDate(CompareValue As String, rgSearch As Range) As Variant
    Dim arSearch() As Variant
    Dim rw As Long, DateMax As Date, TypeFound As Integer
    
    arSearch = rgSearch
    For rw = LBound(arSearch, 1) To UBound(arSearch, 1)
        If arSearch(rw, 1) = CompareValue Then
            TypeFound = 1
            If arSearch(rw, 2) > DateMax Then
                DateMax = arSearch(rw, 2)
            End If
        End If
    Next rw
    If TypeFound = 1 Then
        MaxDate = DateMax
    Else
        MaxDate = "Type not found"
    End If
End Function

Open in new window


You use it as a normal Excel formula like this
=MaxDate(A2,Sheet2!A2:B5)
The first argument is the type to match, and the second argument is the range to search.
If the type is not in the search range, the result is "Type not found"
Format the result cells as dates.

The function must be in a standard module.

As a normal formula you can use
=MAX(INDEX((Sheet2!$A$2:$A$5=A2)*Sheet2!$B$2:$B$5,,))
If no match for type, the result will be 0.
0
 
upobDaPlayaAuthor Commented:
MaxDateFunction does the trick !
0
 
upobDaPlayaAuthor Commented:
Great !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.