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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upobDaPlayaAuthor Commented:
MaxDateFunction does the trick !
0
upobDaPlayaAuthor Commented:
Great !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.