Upgrade to LookUpConcat UDF: Search Multiple Criteria and concatenate.

**DJE** · 04-02-2014, 09:26 PM

The LookUpConcat UDF helped me greatly, so I wanted to contribute this enhanced version.

This version works like LookUpConcat, but allows you to search for multiple criteria and it concatenates all results. The search terms are specified as a comma separated list by default, but you can optionally specify any delimiter.

Code:

Function Multi_LookUpConcat(ByVal SearchList As String, SearchRange As Range, ReturnRange As Range, _
                      Optional SearchListDelimiter As String = ",", _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                  
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
'Parse the SearchList into Strings
' Spaces next to the delimiters will be ignored
Dim SearchString As String
Dim List As String
Dim C1 As Integer
Dim C2 As Integer

  If StrComp(SearchList, "") = 0 Then
    Multi_LookUpConcat = ""

  ElseIf (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    Multi_LookUpConcat = CVErr(xlErrRef)
  
  Else
    
    SearchList = SearchList & SearchListDelimiter   'Ensure that it runs at least once
    C1 = 1
    C2 = InStr(C1, SearchList, SearchListDelimiter)
    
    While C2 > 0
        SearchString = Trim(Mid(SearchList, C1, C2 - C1))

        If Not MatchCase Then SearchString = UCase(SearchString)
        For X = 1 To SearchRange.Count
          If MatchCase Then
            CellVal = SearchRange(X).Value
          Else
            CellVal = UCase(SearchRange(X).Value)
          End If
          ReturnVal = ReturnRange(X).Value
          If MatchWhole And CellVal = SearchString Then
            If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
            Result = Result & Delimiter & ReturnVal
          ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
            If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
            Result = Result & Delimiter & ReturnVal
          End If
Continue:
        Next
   
    ' Advance the pointers to search for the next element
    C1 = C2 + 1
    C2 = InStr(C1, SearchList, SearchListDelimiter)
    Wend
    
    Multi_LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  
  End If
  
End Function

**Admin** · 04-02-2014, 10:10 PM

**pub** · 12-21-2017, 05:58 PM

@DJE, I've attached below an excel file where I used your VBA code above.
In F1 ... H4 cells I would like name lists (separated by comma) with the values from column C taking into account the two criteria listed in the cells: F1, G1, H1 and E2...E4.

**station** · 05-23-2018, 12:18 AM

Originally Posted by pub

@DJE, I've attached below an excel file where I used your VBA code above.
In F1 ... H4 cells I would like name lists (separated by comma) with the values from column C taking into account the two criteria listed in the cells: F1, G1, H1 and E2...E4.

Hi,

I tried the multiple lookup_Concat and I get a REF! error

Given the same data in the example XLSX file, I used this formula in cell F3:

=Multi_LookUpConcat("F1;E3",$A$2:$B$50,$C$2:$C$50,";",", ")

F1 and E3 are the two criteria to search for.
$A$2:$B$50 are the columns these data should be found.
$C$2:$C$50 is the range to pull results from

I can only successfully concat with one criteria. Any help would be appreciated.