IF statement inside QUERY in Google Sheets

1
votes

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!

Screenshot

2
google-sheetsgoogle-sheets-query
You have to take the if statement outside your query as in this answer: stackoverflow.com/questions/50070600/if-statement-in-query – Tom Sharpe
Sorry for the (very) late reply. I just tried to implement the solution from the other answer and it didn't quite work for me. What I'm aiming to achieve is: In column E (only in this column) the query should display the value of Col4 IF the value in Col2 is <=3 (less than or equal 3). Otherwise IF the value in Col2 is >3 the query should display (in the same column E) the value of Col3 – Leonel Romero

2 Answers

2
votes

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)})

enter image description here

0
votes

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)