Nov 02 2020 11:09 AM - edited Nov 02 2020 11:10 AM
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.
Nov 02 2020 11:18 AM
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)))
Nov 02 2020 11:37 AM
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,""))
Nov 02 2020 12:05 PM
@Sergei Baklan Unfortunately, the FILTER function is not supported in the version of Excel I'm using (2016).
Nov 02 2020 12:06 PM - edited Nov 02 2020 12:08 PM
@Hans Vogelaar Thanks. I tried it, but got a #NAME? error as a result.
Nov 02 2020 12:30 PM
Thus you don't have TEXTJOIN() as well.
Nov 02 2020 12:36 PM
@Sergei Baklan Yes, that's correct. Textjoin is not available in this version.
Nov 02 2020 12:43 PM
SolutionPress 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.
Nov 02 2020 01:04 PM
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.
Nov 02 2020 02:35 PM
@Hans Vogelaar That worked beautifully. Thank you!
Nov 04 2020 01:14 PM - edited Nov 04 2020 01:15 PM
@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?
Nov 04 2020 01:18 PM
Could you attach a sample workbook that demonstrates this problem?
Nov 04 2020 02:31 PM
@Hans Vogelaar Nevermind, I changed the formula in a way that was creating the error. All is fine. Thanks again!
Nov 02 2020 12:43 PM
SolutionPress 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.