Google Sheets Two Lists as Criteria for Query

1
votes

I have the following query which is working fine. However I'd like to adde some additional logic if possible where if the there is a value in K3 then ideally the query would not use WHERE R = '"&$K2&"' in the query below but rather use WHERE D = '"&$K3&"'.

Here is a sample sheet. https://docs.google.com/spreadsheets/d/1Lt0RzwtuqIrxWYmWnYK4nh0JN2WdM0icpYF9oXGRfB8/edit?usp=sharing

QUERY(Transactions,"SELECT C,D,B,E WHERE R = '"&$K2&"' AND B > date '"&TEXT($C$2,"yyyy-mm-dd")&"' AND B <= date '"&TEXT($C$3,"yyyy-mm-dd")&"' ",1)

1
google-sheets

1 Answers

2
votes

Try

=QUERY(Transactions,"SELECT C,D,B,E WHERE "&if(K3="",L2,L3)&" = '"&if(K3="",K2,K3)&"' AND B > date '"&TEXT($C$2,"yyyy-mm-dd")&"' AND B <= date '"&TEXT($C$3,"yyyy-mm-dd")&"' ",1)

with R and D in L2/L3