Google sheets query formula with multiple conditions-CodePudding

I have a google sheets query

=TEXTJOIN(CHAR(10),1,QUERY('.Data'!$A$2:$AC, "SELECT E, AB WHERE (B = 'test' and AB !='')"))

I am expecting that the query would select all columns where B is test and AB does not have anything. However it does not. Example data:

Column B (1st row) = test  
Column B (2nd row) = test  
Column B (3rd row) = smile  
  
Column E (1st row) = E1  
Column E (2nd row) = E2  
Column E (3rd row) = E3

Column AB (1st row) = AB1  
Column AB (2nd row) = ''  
Column AB (3rd row) = ''

I expect to get: E1 AB1

What I get is:

E1 AB1
E2

Also tried "is not null" but that did not work as well. I searched around but was not able to figure out what I am missing.

CodePudding user response：

Try below QUERY() function then use TEXTJOIN().

=QUERY(A:H,"select E, G where (B='test') and (G is not null)")

If you want to use not equal != operator then use-

=QUERY(A:H,"select E, G where (B='test') and (G !='')")

And the join values-

=JOIN(CHAR(10),QUERY(A:H,"select E, G where (B='test') and (G !='')"))