SOLVED

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

Occasional Contributor

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

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

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

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

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

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,""))``

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

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

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

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

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

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

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

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

Best Response confirmed by wiscy (Occasional Contributor)
Solution

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

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.

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

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.

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

@Hans Vogelaar  That worked beautifully.  Thank you!

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

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

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

Could you attach a sample workbook that demonstrates this problem?

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

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