IF statement inside QUERY in Google Sheets

Question

I have a google sheet where I have several projects listed. With a QUERY function I am retrieving information into another sheet for reporting purposes.

The formula I am using is: =QUERY($A$2:$D$8,"IF(OR($C2=1,$C2=2,$C2=3), Select A, B, D, Select A, B, C)",0)

What I am struggling to do is placing an IF statement inside the QUERY function.

I would like to get the Date 2 (Col4) value if the Status (Col2) value is "1", "2" or "3". Otherwise, if the Status (Col2) value is "4", "5" or "6" I would like the Query to return the Date 1 (Col3)

Any help would be appreiated. Thanks!

Answer 1

The formula would be like this:

=ArrayFormula(query({A2:B8,if(B2:B8<=3,D2:D8,C2:C8)},"select *"))

But the query isn't actually doing anything.

So you could just use:

=ArrayFormula({A2:B8,if(B2:B8<=3,D2:D8,C2:C8)})

Answer 2

So it is possible to include an IF statement into a query. The main thing to remember is that the query is text, so you have to end the quotations include your reference, and then restart the quotations. It's just like creating a sentence via formula. For an example, you would add the following text for a secondary condition:

"&if(T3="",""," and H = '"&T3&"'")&"

I wanted the ability to narrow down the results conditionally. So if I have a value in T3, then the condition for column H is added into my query. Otherwise, it is left out.

In case I messed something up or am not making sense, here's my actual complete formula. I also have the query using filters that reside in Q3,R3, and S3.

=query(Transactions!B1:H, "select B,C,D,E,F,G where B > date '"&TEXT(DATEVALUE(Q3),"yyyy-mm-dd")&"' and B <= date '"&TEXT(DATEVALUE(R3),"yyyy-mm-dd")&"' and D = '"&S3&"'"&if(T3="",""," and H = '"&T3&"'")&" order by B desc limit 50",1)