This might do it - where do you want the result?
I used Column M with subject in row 3 and objects in rows 9,10 and 11
Sub Match4(Subject As String): Dim Object() As String, BestMatch As Range
Dim r As Long, n As Long, x As Long, I As Integer
r = 9: Do Until Range("M" & r + n) = "": n = n + 1: Loop
ReDim Object(n + 1, 1)
For n = 1 To UBound(Object) - 1
Object(n, 0) = Range("M" & r + n - 1)
If Subject = Object(n, 0) Then Object(n, 1) = Len(Subject): GoTo GetNext
For x = 1 To Len(Subject)
If Mid(Object(n, 0), 1, x) <> Mid(Subject, 1, x) Then Object(n, 1) = x - 1
Next x
GetNext: Next n
For n = 1 To UBound(Object) - 1
If Object(n, 1) > I Then
I = Object(n, 1)
Set BestMatch = Range("M" & r + n - 1)
End If: Next n
End Sub
Sub Test4(): Dim S As String
S = Range("M" & 3): Match4 (S)
End Sub
Bookmarks