0

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?

1 Answer 1

0

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

Responses


Query

query

2
  • thanks man, it work i use =QUERY(Responses!B1:I, "Select * where G matches "&"'"&join("|"B:B)&"'") Jun 21, 2021 at 10:33
  • Nice simplification, but there's a typo in the join function-missing argument separator. It should read join("|",B:B)
    – Tedinoz
    Jun 22, 2021 at 0:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.