QUERY syntax using cell reference more than 1

Question

I'm trying to use a cell reference instead of static values.

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

I use code like this because the data is varchar, but it's so many cell references:

=QUERY(Responses!B1:I, "Select B where G = '"&B1&"',or G = '"&B2&"',or G = '"&B3&"',or G = '"&B4&"',or G = '"&B5&"',or G = '"&B6&"'")

How can I make it simple - like the first query?

Answer 1

You want to query a range where column G equals any value ina separate range.

User @Zolley in StackOverflow Query with range of values for WHERE clause? provides an answer for this. The answer requires a helper cell...

Helper Cell

• In cell C1 (or any other cell that you choose) enter this formula:
  =TEXTJOIN("|",1,B:B)

• if you prefer to limit the range to only those rows that contain data, you could use this formula instead.
  =TEXTJOIN("|",1,indirect(address(1,2,4,true)&":"&address(counta(B1:B1000),2,4,true)))

Query

• Enter this where you want your output:
  =QUERY(Responses!B1:I,"Select * WHERE G matches '^.*(" & C1 & ").*$'",1)

---

Responses

---

Query