Comparing Text in a Cell to a Range of Text

[ajwooden32]

I'm looking for a formula to compare a range of teams in the 1st column, to a single cell of teams scheduled to play games in the 3rd column, that will provide me a list of teams with no games scheduled in the 4th column. Sometimes the teams listed under scheduled to play games are not always in order so the formula needs to be able to compare regardless of the teams order or to be able to sort then compare.

Preferably the results in the 4th column would be sorted automatically.

Thanks in advance!

Teams	Date	Scheduled to Play Games	No Games Scheduled
3rd Boys A	11/2/24	3rd Boys A, 3rd Boys B, 4th Boys A, 4th Boys B, 5th Boys A, 5th Boys B	3rd Girls, 6th Boys A, 6th Boys B
3rd Boys B	11/3/24	3rd Boys B, 4th Boys B, 5th Boys B, 6th Boys B, 6th Boys A, 6th Boys B, 3rd Boys A, 4th Boys A	3rd Girls, 5th Boys A
4th Boys A	11/9/24	5th Boys A, 5th Boys B	3rd Boys A, 3rd Boys B, 3rd Girls, 4th Boys A, 4th Boys B, 6th Boys A, 6th Boys B
4th Boys B	11/10/24	5th Boys A, 5th Boys B, 4th Boys A
5th Boys A	11/16/24	3rd Boys A, 4th Boys A, 5th Boys A, 3rd Girls, 3rd Boys B, 4th Boys B, 5th Boys B
5th Boys B	11/17/24	3rd Boys A, 4th Boys A, 5th Boys A, 6th Boys A, 3rd Boys B, 4th Boys B, 5th Boys B, 6th Boys B
6th Boys A
6th Boys B
3rd Girls

 

[Phuoc]

Try this:

Book1
	A	B	C	D
1	Teams	Date	Scheduled to Play Games	No Games Scheduled
2	3rd Boys A	11/2/2024	3rd Boys A, 3rd Boys B, 4th Boys A, 4th Boys B, 5th Boys A, 5th Boys B	6th Boys A, 6th Boys B, 3rd Girls
3	3rd Boys B	11/3/2024	3rd Boys B, 4th Boys B, 5th Boys B, 6th Boys B, 6th Boys A, 6th Boys B, 3rd Boys A, 4th Boys A	5th Boys A, 3rd Girls
4	4th Boys A	11/9/2024	5th Boys A, 5th Boys B	3rd Boys A, 3rd Boys B, 4th Boys A, 4th Boys B, 6th Boys A, 6th Boys B, 3rd Girls
5	4th Boys B	########	5th Boys A, 5th Boys B, 4th Boys A	3rd Boys A, 3rd Boys B, 4th Boys B, 6th Boys A, 6th Boys B, 3rd Girls
6	5th Boys A	11/16/24	3rd Boys A, 4th Boys A, 5th Boys A, 3rd Girls, 3rd Boys B, 4th Boys B, 5th Boys B	6th Boys A, 6th Boys B
7	5th Boys B	11/17/24	3rd Boys A, 4th Boys A, 5th Boys A, 6th Boys A, 3rd Boys B, 4th Boys B, 5th Boys B, 6th Boys B	3rd Girls
8	6th Boys A
9	6th Boys B
10	3rd Girls
Sheet1

Cell Formulas
Range	Formula
D2:D7	D2	=TEXTJOIN(", ",1,FILTER($A$2:$A$10,ISERROR(SEARCH($A$2:$A$10&",",C2&",")),""))

 

[ajwooden32]

Works perfect when opened in excel. First problem solved!

Now I have a new issue. When I upload this excel file to our organization's google drive the formula that was working gives a #NAME? error now.

Any idea what's wrong with this formula & what I need to change to get it to work in google sheets?

=ARRAY_CONSTRAIN(ARRAYFORMULA(TEXTJOIN(", ",1,_xlws.FILTER($A$2:$A$13,ISERROR(SEARCH($A$2:$A$13&",",C2&",")),""))), 1, 1)

 

[Phuoc]

Try change to:

=TEXTJOIN(", ",1,FILTER($A$2:$A$10,ARRAYFORMULA(ISERROR(SEARCH($A$2:$A$10&",",C2&",")))))

 

[ajwooden32]

@Phuoc Thank you for your help! Both solutions you gave worked!