Mr Excel & excelisfun Trick 98: Extract & Lookup Number When Table Of Numbers Is In A Single Cell

[AKTools]

Hey Bill,

here is another and a bit shorter Methode for your "GetMatch" Function shown in the youtube dueling excel podcast.

Function GetMatch(ByVal LookAt As Range, ByVal LookIn As Range, Optional ByVal Delimiter As String = "-")
    Dim vLookAtSplit As Variant
        vLookAtSplit = Split(LookAt.Value, Delimiter)
    Dim vLookInSplit As Variant
        vLookInSplit = Split(LookIn.Value, Delimiter)
 
    Dim x, y As Integer
    For x = LBound(vLookAtSplit) To UBound(vLookAtSplit)
        For y = LBound(vLookInSplit) To UBound(vLookInSplit)
            If vLookAtSplit(x) = vLookInSplit(y) Then
                GetMatch = GetMatch & " " & vLookAtSplit(x)
            End If
        Next y
    Next x
    GetMatch = Replace(Trim(GetMatch), " ", Delimiter)
End Function