Multiple Value Return Using Lookup

[excel_1317]

Example (2).xlsxRick, please amend your code to provide results as Column E, F, G and H in the attached file.

Code:

Sub ProcessLookUpValues()
  Dim X As Long, Z As Long, Index As Long
  Dim ArrLookUp As Variant, ArrIn As Variant, ArrOut As Variant, Counts As Variant
  Columns("D:G").ClearContents
  ArrLookUp = Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
  ReDim Counts(1 To UBound(ArrLookUp), 1 To 1)
  ArrIn = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim ArrOut(1 To UBound(ArrIn) + UBound(ArrLookUp), 1 To 3)
  For Z = 1 To UBound(ArrLookUp)
    For X = 1 To UBound(ArrIn)
      If UCase(ArrIn(X, 1)) Like "*" & UCase(ArrLookUp(Z, 1)) & "*" Then
        Counts(Z, 1) = Counts(Z, 1) + 1
        Index = Index + 1
        ArrOut(Index, 1) = ArrIn(X, 1)
        ArrOut(Index, 2) = ArrIn(X, 2)
        ArrOut(Index, 3) = ArrLookUp(Z, 1)
      End If
    Next
    Index = Index + 1
  Next
  Range("D1:G1") = Array("Count of Lookup Value", "Result 1", "Result 2", "Result 3 (Lookup Value")
  Range("D2:D" & 1 + UBound(ArrLookUp)) = Counts
  Range("E2:G" & UBound(ArrOut)) = ArrOut
End Sub