Index/Match Question: Looking up two criteria with multiple matching results

wiscy 

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Function ConcatenateIfs(ConcatenateRange As Range, ParamArray Criteria() As Variant) As Variant
    ' Source: EileensLounge.com, August 2014
    Dim i As Long
    Dim c As Long
    Dim n As Long
    Dim f As Boolean
    Dim Separator As String
    Dim strResult As String
    On Error GoTo ErrHandler
    n = UBound(Criteria)
    If n < 2 Then
        ' Too few arguments
        GoTo ErrHandler
    End If
    If n Mod 2 = 0 Then
        ' Separator specified explicitly
        Separator = Criteria(n)
    Else
        ' Use default separator
        Separator = ","
    End If
    ' Loop through the cells of the concatenate range
    For i = 1 To ConcatenateRange.Count
        ' Start by assuming that we have a match
        f = True
        ' Loop through the conditions
        For c = 0 To n - 1 Step 2
            ' Does cell in criteria range match the condition?
            If Criteria(c).Cells(i).Value <> Criteria(c + 1) Then
                ' If not, we don't have a match
                f = False
                Exit For
            End If
        Next c
        ' Were all criteria satisfied?
        If f Then
            ' If so, add separator and value to result
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        ' Remove first separator
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIfs = strResult
    Exit Function
ErrHandler:
    ConcatenateIfs = CVErr(xlErrValue)
End Function

Switch back to Excel.

Enter the following formula in C2:

=ConcatenateIfs('Data 2'!$A$2:$A$71,'Data 2'!$B$2:$B$71,A2,'Data 2'!$C$2:$C$71,B2, ", ")

Fill or copy down.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

See the attached version.