CODEWITHSUNDEEP
arraysgoogle-sheetsfiltergoogle-sheets-formulagoogle-query-language
Boris Baublys
Boris Baublys

Reputation: 1203

QUERY with cell reference containing a number

The formula in cell H2 works fine but the same one in cell F2 does not. enter image description here

Their difference is that in F2 the second parameter for the query is taken from the cells with numbers.

=QUERY($A$3:$D;"SELECT A,B,C,D
WHERE 
A = '"&$A$1&"'
AND
B = '"&$B$1&"'")

The Filter() is not an option because I do not need to display all columns. I would be grateful for ideas.

Upvotes: 1

Views: 1601

Answers (1)

player0
player0

Reputation: 1

numeric numbers in query do not require single quote wrapping

=QUERY(A3:D;
 "select A,B,C,D
  where A = '"&A1&"'
    and B = "&B1)

with filter you can use constructed {} arrays

=FILTER({A3:B\ D3:D}; A3:A=A1; B3:B=B1)

also note that your F2 formula will work if you set formatting of B column to Plain text

=QUERY(A2:D; 
 "select A,B,D 
  where "&TEXTJOIN(" and "; 1; "1=1";
    IF(A1="";; "A = '"&A1&"'");
    IF(B1="House";; "B = "&B1);
    IF(D1="";; "D = '"&D1&"'")); 1)

enter image description here

spreadsheet demo

Upvotes: 2

Related Questions