0

This formula is omitting results that contain parenthesis. I've tried SUBSTITUTE'ing the parenthesis with CHAR(40) & CHAR(41) to no avail. I've tried changing my QUERY from MATCHES to = and also CONTAINS and none of those seem to work either.

Is there some easy/obvious solution to escape these parenthesis that I'm overlooking?

The array that has the data containing parenthesis is X4:X.

=QUERY({'List 3v3 Teams'!B1:J},"SELECT Col1,Col9 WHERE Col1 matches '"&TEXTJOIN("|",TRUE,X4:X)&"' AND NOT Col1 matches '"&TEXTJOIN("|",TRUE,Setup!A3:A52)&"' AND Col1 IS NOT NULL AND Col2 IS NOT NULL ORDER BY Col9 DESC",1)

My sheet - formula is in Suggested!AA3

EDIT: Used FILTER instead. But I'd still like to know how to overcome this parenthesis issue. 

=UNIQUE(FILTER({'List 3v3 Teams'!B2:B,'List 3v3 Teams'!J2:J},COUNTIFS(X4:X,'List 3v3 Teams'!B2:B),NOT(COUNTIF(Setup!A3:A52,'List 3v3 Teams'!B2:B))))
TC76
  • 860
  • 1
  • 8
  • 17

1 Answers1

2

I would definitely go for the filter solution, but since you are interested in the adaptation of the query, you could try to remove the parenthesis with regexreplace. This should yield the same result as the filter() function...

=unique(ArrayFormula(QUERY({regexreplace('List 3v3 Teams'!B1:B,"\(|\)",) , 'List 3v3 Teams'!B1:J},"SELECT Col2,Col10 WHERE Col1 matches '"&TEXTJOIN("|",TRUE,REGEXREPLACE(X4:X,  "\(|\)",))&"' AND NOT Col1 matches '"&TEXTJOIN("|",TRUE,Setup!A3:A52)&"' AND Col1 <>'' AND Col3 IS NOT NULL ORDER BY Col9 DESC",1)))
JPV
  • 26,499
  • 4
  • 33
  • 48