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

Occasional Contributor

Suggestions on a formula to lookup two criteria on sheet 1 against a table of data on sheet 2 that would report all results from sheet 2 matching the combination of two criteria would be appreciated.  Example worksheet attached.

12 Replies


That could be

=TEXTJOIN(",",1,FILTER('Data 2'!$A$2:$A$71,('Data 2'!$B$2:$B$71=A2)*('Data 2'!$C$2:$C$71=B2)))


If you have Excel 2019 or Excel in Office 365:

Enter the following formula in C2 and confirm it with Ctrl+Shift+Enter to turn it into an array formula:


=TEXTJOIN(", ",TRUE,IF(('Data 2'!$B$2:$B$71=A2)*('Data 2'!$C$2:$C$71=B2),'Data 2'!$A$2:$A$71,""))

@Sergei Baklan  Unfortunately, the FILTER function is not supported in the version of Excel I'm using (2016).

@Hans Vogelaar  Thanks.  I tried it, but got a #NAME? error as a result.


Thus you don't have TEXTJOIN() as well.

@Sergei Baklan  Yes, that's correct.  Textjoin is not available in this version.

Best Response confirmed by wiscy (Occasional Contributor)


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:, 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)
        ' 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
    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.


Another couple of variants if with data model and Power Query


With data model - add both tables to it, create measure Result as

=CONCATENATEX(FILTER(Data1, Data1[Category]&Data1[Ctry]=Data2[Category]&Data2[Ctry]),Data1[ID],",")

and create PivotTable on first table adding this measure.


With Power Query - merge first table with second one, extract ID column as list and combine as text.

@Hans Vogelaar  That worked beautifully.  Thank you!

@Hans Vogelaar  When using this script in some cases I get a #Value! error until I press CRTL-SHIFT-Enter to change to an array formula; however, then the look up result reports one value even when there are multiple matches that should be returned.  Any ideas what might cause that?


Could you attach a sample workbook that demonstrates this problem?

@Hans Vogelaar  Nevermind, I changed the formula in a way that was creating the error.  All is fine.  Thanks again!