Formula to concatenate with multiple if conditions

[Manatarms]

Hi can anyone help please, I am trying to create a formula that looks down a list of data and concatenates the names of two if conditions are met? So in the example of shop is A and region is North then concatenate names save and Mike. Thanks!

 

[gordsky]

Hi can anyone help please, I am trying to create a formula that looks down a list of data and concatenates the names of two if conditions are met? So in the example of shop is A and region is North then concatenate names save and Mike. Thanks!

I cant test this as my version of excel doesnt have TEXTJOIN but I believe 2016 does.
Try putting this in your output cell. Its an ARRAY formula so will need to be entered with Ctrl+Shift+Enter

=TEXTJOIN(",",TRUE,IF(($A2:$A9="A")*($B2:$B9="North"),$C2:$C9,""))

 

[Fluff]

I cant test this as my version of excel doesnt have TEXTJOIN but I believe 2016 does.

But your profile shows you are using 2016.
However 2016 doesn't have textjoin.

 

[gordsky]

But your profile shows you are using 2016.
However 2016 doesn't have textjoin.

temporarily on a different machine, was sure i had used textjoin on my personal comp. guess it must have been on the works one. always happy to admit when wrong

 

[jtakw]

Hi,

This is Ugly, and Only works if there are no more than 2 matches.
If there are more than 2 matches, I would think a UDF or VBA solution is more suitable:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1								North	South
2	A	North	Dave				A	Dave,Mike
3	B	South	Bob				B		Bob,Larry
4	C	North	Tony				C	Tony
5	D	South	Gary				D		Gary
6	E	North	Steve				E	Steve
7	F	South	Trevor				F		Trevor
8	B	South	Larry
9	A	North	Mike
Sheet5

Cell Formulas
Range	Formula
H2:I7	H2	=SUBSTITUTE(TRIM(IFERROR(INDEX($C$2:$C$9,MATCH($G2&H$1,$A$2:$A$9&$B$2:$B$9,0)),"")&" "&IF(IFERROR(LOOKUP(2,1/SEARCH($G2&H$1,$A$2:$A$9&$B$2:$B$9),$C$2:$C$9),"")=IFERROR(INDEX($C$2:$C$9,MATCH($G2&H$1,$A$2:$A$9&$B$2:$B$9,0)),""),"",IFERROR(LOOKUP(2,1/SEARCH($G2&H$1,$A$2:$A$9&$B$2:$B$9),$C$2:$C$9),"")))," ",",")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Manatarms]

Hi,

This is Ugly, and Only works if there are no more than 2 matches.
If there are more than 2 matches, I would think a UDF or VBA solution is more suitable:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1								North	South
2	A	North	Dave				A	Dave,Mike
3	B	South	Bob				B		Bob,Larry
4	C	North	Tony				C	Tony
5	D	South	Gary				D		Gary
6	E	North	Steve				E	Steve
7	F	South	Trevor				F		Trevor
8	B	South	Larry
9	A	North	Mike
Sheet5

Cell Formulas
Range	Formula
H2:I7	H2	=SUBSTITUTE(TRIM(IFERROR(INDEX($C$2:$C$9,MATCH($G2&H$1,$A$2:$A$9&$B$2:$B$9,0)),"")&" "&IF(IFERROR(LOOKUP(2,1/SEARCH($G2&H$1,$A$2:$A$9&$B$2:$B$9),$C$2:$C$9),"")=IFERROR(INDEX($C$2:$C$9,MATCH($G2&H$1,$A$2:$A$9&$B$2:$B$9,0)),""),"",IFERROR(LOOKUP(2,1/SEARCH($G2&H$1,$A$2:$A$9&$B$2:$B$9),$C$2:$C$9),"")))," ",",")
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks to you both, unfortunately it seems textjoin isn’t available for excel 2016…

Thanks to Jtakw but there are more than 2 matches in my data so this won’t quiet work.

Any other ideas or leads ? Thanks

 

[Peter_SSs]

Any other ideas or leads ?

You could employ a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Who(rData As Range, sShop As String, sRegion As String) As String
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  a = rData.Value
  For i = 1 To UBound(a)
    d(a(i, 1) & "|" & a(i, 2)) = d(a(i, 1) & "|" & a(i, 2)) & "," & a(i, 3)
  Next i
  Who = Mid(d(sShop & "|" & sRegion), 2)
End Function

Manatarms.xlsm
	A	B	C	D	E	F	G	H	I
1	Shop	Region	Name					North	South
2	A	North	Dave				A	Dave,Mike
3	B	South	Bob				B		Bob,Larry
4	C	North	Tony				C	Tony
5	D	South	Gary				D		Gary
6	E	North	Steve				E	Steve
7	F	South	Trevor				F		Trevor
8	B	South	Larry
9	A	North	Mike
10
Sheet1

Cell Formulas
Range	Formula
H2:I7	H2	=Who($A$2:$C$9,$G2,H$1)