google-sheetsgoogle-sheets-query

QUERY syntax using cell reference

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"& $B1 &)

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)?

Solution

  • 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 & 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 '^.\*($" & B1 & ").\*$'") - I have not tried it, but it helped in another post: Query with range of values for WHERE clause?