QUERY syntax using cell reference

Question

I m having trouble figuring out a fairly simple QUERY statement in Google Spreadsheets  I m trying to use a cell reference instead of static values and I m running into trouble  Below it the code I m using  but I keep getting a  Error  Formula parse error     QUERY Responses B1 I   Select B where G contains  amp   B1  amp     I m sure it is a simple error  but can someone please show me how to write the above so the QUERY is pulling data from B where G contains the value in cell B1  cell reference

User · Answer

I know this is an old thread but I had the same question as the OP and found the answer:

You are nearly there, the way you can include cell references in query language is to wrap the entire thing in speech marks. Because the whole query is written in speech marks you will need to alternate between ' and " as shown below.

What you would need is this:

=QUERY(Responses!B1:I, "Select B where G contains '"& B1 &"' ")

If you then wanted to refer to multiple cells you could add more like this

=QUERY(Responses!B1:I, "Select B where G contains '"& B1 &"' and G contains '"& B2 &"' ")

The above would filter down your results further based on the contents of B1 and B2.

User · Answer

I only have a workaround here  In this special case  I would use the FILTER function instead of QUERY    FILTER Responses B B Responses G G B1    Assuming that your data is on the  Responses  sheet  but your condition  cell reference  is in the actual sheet s B1 cell   Hope it helps   UPDATE   After some search for the original question  The problem with your formula is definitely the second  amp  sign which assumes that you would like to concatenate something more to your WHERE statement  Try to remove it  If it still doesn t work  then try this    QUERY Responses B1 I   Select B where G matches           amp  B1  amp            - I have not tried it  but it helped in another post  Query with range of values for WHERE clause

User · Answer

Old Thread but I found this on my journey to the below answer and figure someone else might need it too    IFERROR ArrayFormula query index Sheet3 A C amp      select  Where Col1     amp B1 amp          ARRAYFORMULA             no cells   match        Here is a simply built text Filter from a 3 column data set  A B and C  located in  sheet3  into the current sheet and calling a comparison to a cell from the current sheet to filter within Col1 A     This bit is just to get rid of the  N A error if the filter turns up no results   ARRAYFORMULA             no cells   match

User · Answer

I found out that single quote   double quote   wrapped in ampersands did work  So  for me it looks like this    QUERY  Youth Conference Registration  C Y  select C where Y      amp A1 amp      0

User · Answer

Copied from Web Applications      QUERY Responses B1 I   Select B where G contains    amp  B1 amp

User · Answer

Here is working code   QUERY Sheet1  A1  B581   quot select B where A     quot  amp A1 amp  quot   quot   In this scenario I needed the interval to stay fixed and the reference value to change when I drag it

User · Answer

To make it work with both text and numbers  Exact match   query D E  quot select   where D like   quot  amp C1 amp  quot   quot   0   Convert search string to lowercase   query D E  quot select   where D like lower   quot  amp C1 amp  quot    quot   0   Convert to lowercase and contain part of the search string   query D E  quot select   where D like lower    quot  amp C1 amp  quot     quot   0    A1                                 query formula yellow   A B     result area green   C1         search area blue   D E           data area If you get error when the input is text and not numbers  move the data and delete the  now empty  columns  Then move the data back

User · Answer

none of the above answers worked for me  This one did    QUERY Copy A1 AP   select AP  E  F  AO where AP   amp E1 amp     1

[google-sheets] QUERY syntax using cell reference

Examples related to google-sheets

Examples related to google-sheets-query