CODEWITHSUNDEEP
google-sheetsdropdowngoogle-sheets-formulagoogle-sheets-query
Alex
Alex

Reputation: 79

Google Sheets QUERY with dropdown menu + multiple conditions

I have this kind of table on a tab (called "Log"):

A     B     C     D    E   F    G    H
a1    b1    c1    d1   5   f1   g1   h1
a2    b1    c2    d1   3   f2   g2   
a3    b2    c1    d2   4   f3   g3   h2
a4    b1          d1   5   f4   g4 
a5    b2    c3    d1   3   f5   g5   h3

On another tab (called "Watch") of the same file I have a dropdwon menu with all the "D"s.

I'm trying, on the "Watch" tab, with the QUERY function to visualize C,E,G and H. C,E and H have to always be visualized while I need G only if "E"s are between "1" and "3".

The closest I got was this:

=QUERY(Log!B:H, "SELECT C,E,H,G WHERE D='"&B1&"' and H is not null and E<=3")

but it shows only where "E"s are <=3 ignoring the choice from the dropdown menu (WHERE D='"&B1&"')

Upvotes: 0

Views: 889

Answers (2)

user11982798
user11982798

Reputation: 1908

Try with this:

=filter({Log!C1:C5,Log!E1:E5,Log!H1:H5,arrayformula(if((Log!H1:H5="")*(Log!E1:E5<=3),Log!G1:G5,""))}, Log!D1:D5="d1")

or

=filter({Log!C1:C5,Log!E1:E5,Log!H1:H5,arrayformula(if((Log!H1:H5="")*(Log!E1:E5<=3),Log!G1:G5,""))}, Log!D1:D5 = B1)

or 

=filter({Log!C1:C5,Log!E1:E5, Log!H1:H5,if((Log!H1:H5="")*(Log!E1:E5<=3)=1,Log!G1:G5,"")},Log!D1:D5="d1")

The Result:

c1  5   h1  
c2  3       g2
    5       
c3  3   h3

Upvotes: 0

player0
player0

Reputation: 1

try:

=QUERY(Log!B:H, 
 "select C,E,H,G 
  where lower(D) = '"&TRIM(LOWER(B1))&"' 
    and H is not null 
    and E<=3", 0)

Upvotes: 1

Related Questions