SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1845889%22%20slang%3D%22en-US%22%3EIndex%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1845889%22%20slang%3D%22en-US%22%3E%3CP%3ESuggestions%20on%20a%20formula%20to%20lookup%20two%20criteria%20on%20sheet%201%20against%20a%20table%20of%20data%20on%20sheet%202%20that%20would%20report%20all%20results%20from%20sheet%202%20matching%20the%20combination%20of%20two%20criteria%20would%20be%20appreciated.%26nbsp%3B%20Example%20worksheet%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1845889%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1845945%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1845945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780339%22%20target%3D%22_blank%22%3E%40wiscy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%22%2C1%2CFILTER('Data%202'!%24A%242%3A%24A%2471%2C('Data%202'!%24B%242%3A%24B%2471%3DA2)*('Data%202'!%24C%242%3A%24C%2471%3DB2)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846032%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846032%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780339%22%20target%3D%22_blank%22%3E%40wiscy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%202019%20or%20Excel%20in%20Office%20365%3A%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20C2%20and%20confirm%20it%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20into%20an%20array%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(('Data%202'!%24B%242%3A%24B%2471%3DA2)*('Data%202'!%24C%242%3A%24C%2471%3DB2)%2C'Data%202'!%24A%242%3A%24A%2471%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846247%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Unfortunately%2C%20the%20FILTER%20function%20is%20not%20supported%20in%20the%20version%20of%20Excel%20I'm%20using%20(2016).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846248%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846248%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Thanks.%26nbsp%3B%20I%20tried%20it%2C%20but%20got%20a%20%23NAME%3F%20error%20as%20a%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846316%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780339%22%20target%3D%22_blank%22%3E%40wiscy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20you%20don't%20have%20TEXTJOIN()%20as%20well.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1846337%22%20slang%3D%22en-US%22%3ERe%3A%20Index%2FMatch%20Question%3A%20Looking%20up%20two%20criteria%20with%20multiple%20matching%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846337%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Yes%2C%20that's%20correct.%26nbsp%3B%20Textjoin%20is%20not%20available%20in%20this%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@wiscy 

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)))

@wiscy 

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.

@wiscy 

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)
Solution

@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.

@wiscy 

Another couple of variants if with data model and Power Query

image.png

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?

@wiscy 

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!