Having trouble with TEXTJOIN and FILTER

[zjamespryor]

Hello Spreadsheet Experts,
I am building this in Google Sheets. I am having trouble coming up with a formula to filter through some data and join the results together in a single cell. In the spreadsheet below, the data being collected by a Google Form is on the right. On the left I've put my desired results for how I'd like to filter out the data on another sheet. I've included a small table below that as a place to figure out the correct formula. Thank you in advance for any help you can offer.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Group Selection:	Group 1
2	Campus	Gold Partner	Silver Partner	Bronze Partner		Name	California	Florida	New York	North Carolina	Virginia
3	California	James	Nancy			James	Gold		Bronze		Silver
4	Florida	Chris, Susan		Nancy		Chris		Gold	Silver	Bronze
5	New York		Chris	James		Susan		Gold		Bronze	Silver
6						Nancy	Silver	Bronze		Gold
7
8
9	Campus	Gold Partner	Silver Partner	Bronze Partner		Group 1	Group 2
10						California	North Carolina
11						Florida	Virginia
12						New York
13
14
Sheet1

Cell Formulas
Range	Formula
A3:A5	A3	=IFERROR(INDEX(F10:G12,,MATCH(B1,F9:G9,0)),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$F$9:$G$9

 

[KRice]

Try this in B3 and copy throughout your results table in Sheets:

=IFERROR(TEXTJOIN(", ","",FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")

 

[zjamespryor]

Try this in B3 and copy throughout your results table in Sheets:

=IFERROR(TEXTJOIN(", ","",FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")

Hey Kirk, thanks for the help. The result is a blank cell, it doesn't show any values.

 

[KRice]

Do you have Excel 365? (You should update your profile details to show this). If so, try this and let me know what you see in Excel.

Mrexcel_20220916b.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Group Selection:	Group 1
2	Campus	Gold Partner	Silver Partner	Bronze Partner		Name	California	Florida	New York	North Carolina	Virginia
3	California	James	Nancy			James	Gold		Bronze		Silver
4	Florida	Chris, Susan		Nancy		Chris		Gold	Silver	Bronze
5	New York		Chris	James		Susan		Gold		Bronze	Silver
6	North Carolina	Nancy		Chris, Susan		Nancy	Silver	Bronze		Gold
7	Virginia		James, Susan
Sheet3

Cell Formulas
Range	Formula
A3:A7	A3	=TRANSPOSE(G2:K2)
B3:D7	B3	=TEXTJOIN(", ",,FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1),""))
Dynamic array formulas.

 

[KRice]

In Sheets, here is what I have in...
A3:

=ARRAY_CONSTRAIN(ARRAYFORMULA(TRANSPOSE(G2:K2)), 5, 1)

B3 and copied throughout results table:

=IFERROR(TEXTJOIN(", ","",FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")

There are some minor differences between the Excel version and the Sheets version.

 

[zjamespryor]

Thanks, the sheets version works great! Is there a difference to using TRUE vs "" in the text join?

 

[KRice]

Good question. That should normally be a 1 or TRUE if you want to avoid having TEXTJOIN include extra delimiters when the list of items to concatenate is empty. But in this case, the nested FILTER functions eliminate any empty array elements (the array is "tightened up"). So a cleaner version is to eliminate the 2nd argument altogether. It's better to avoid the quotation marks in my earlier post...Excel complains about them, but Sheets is okay with them. Stick with 1/0 or TRUE/FALSE, unless you know the array passed to TEXTJOIN is cleaned of empty elements (the case here), in which case the 2nd argument can be eliminated (except for a placeholder indicated by the double comma).

=IFERROR(TEXTJOIN(", ",,FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")

 

[zjamespryor]

Good question. That should normally be a 1 or TRUE if you want to avoid having TEXTJOIN include extra delimiters when the list of items to concatenate is empty. But in this case, the nested FILTER functions eliminate any empty array elements (the array is "tightened up"). So a cleaner version is to eliminate the 2nd argument altogether. It's better to avoid the quotation marks in my earlier post...Excel complains about them, but Sheets is okay with them. Stick with 1/0 or TRUE/FALSE, unless you know the array passed to TEXTJOIN is cleaned of empty elements (the case here), in which the 2nd argument can be eliminated (except for a placeholder indicated by the double comma).

=IFERROR(TEXTJOIN(", ",,FILTER($F$3:$F$6,FILTER($G$3:$K$6,$G$2:$K$2=$A3)=LEFT(B$2,SEARCH(" ",B$2)-1))),"")

Thank you for the explanation.

 

[KRice]

I'm happy to help.