2

I'm trying to get a query based on a tri-state. The possible values are TRUE, FALSE, 'Ignore' or blank. I want this to select all names (B) and emails (C) where a flag column is set to False or blank.

=QUERY(Roster!B3:T, "SELECT B,C WHERE M<>TRUE AND M<>'IGNORE'")

I've also tried

=QUERY(Roster!B3:T, "SELECT B,C WHERE M=FALSE OR M=''")

and

=QUERY(Roster!B3:T, "SELECT B,C WHERE M=FALSE OR M IS NULL")

but none gives me all the people who don't have TRUE or Ignore, and do have FALSE or blank.

Is there a way to actually do TRUE/FALSE/OTHER column query conditions, or is this an impossible task?

  • If you need further help create a demo spreadsheet including sample data, share it with anyone with the link to view and add the link to the question. – Rubén Jun 5 '18 at 18:12
1

Google Query Language inferes the column data type and the values that are not of the inferred data type are ignored.

TRUE and FALSE are booleans but OTHER is string. Depending on the order of the values of one type or the other are being ignored.

0

while regular =OR =AND returns booleans, OR AND in =QUERY is different, so in other words almost anything is possible:

=QUERY(Roster!B3:T; "select B, C where M ='"&"FALSE"&"' OR M ='"&""&"'"; 0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.