-1

I am trying to run multiple queries on a table using Google query.

I have been trying to sparse the dataset using the a,b,c columns using multiple criteria for each column using an AND combiner.

If one criterion is removed then I want the remaining criteria to be used but as Google query from what I can tell requires an AND between each match.

If I remove the criteria on one column I also need to remove the AND depending on which criteria I remove. Is it possible to wrap all my criteria in one AND request like where AND (a matches 1000, b matches 11, c matches 44)?

Formula contained in the spreadsheet (refer image below):

"QUERY(
$A$1:$D$7,
""select A,B,C,D 
where  
""&IF($A$10="""",,""A MATCHES '""&$A$10&""'AND"")&""
""&IF($B$10="""",,""B MATCHES '""&$B$10&""'"")&""
""&IF(AND($A$10<>"""",),,IF(AND(OR($A$10="""",$A$10<>""""),$B$10<>"""",$C$10<>""""),""AND"",)&""
""&IF($C$10="""",,""C MATCHES '""&$C$10&""'"")&""
""))"

Image of the tables:

image of tables

Here is a link to the file: https://docs.google.com/spreadsheets/d/1MOTLqfLMgtU0F_T6TwjHkTAX2qecwH0FkOFY5ZDEmac/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
Cameron Macleod
  • 1
  • 1
  • Consider adding a sample screenshot, and editing your question to make it more readable. :) –  May 22 '20 at 08:33
  • 1
    **DO NOT post images of code, data, error messages, etc.** - copy or type the text into the question. [ask] You are required to post a [mcve] here, **within your question**, and [not a link](https://meta.stackoverflow.com/questions/254428/something-in-my-web-site-or-project-doesnt-work-can-i-just-paste-a-link-to-it) to any third party site. @Madhurjya – Rob May 22 '20 at 09:26

1 Answers1

0

try:

=QUERY(A2:D8,
 "where "&TEXTJOIN(" and ", 1,   
 IF(A11="",,"A matches '"&A11&"'"), 
 IF(B11="",,"B matches '"&B11&"'"),
 IF(C11="",,"C matches '"&C11&"'")))
player0
  • 124,011
  • 12
  • 67
  • 124