Google-sheets – Trying to Query a list against another list

formulasgoogle sheetsgoogle-sheets-query

First time ever using stack exchange so please excuse any formatting problems.

I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains:

  • A date range
  • A list of items
  • True/False checkboxes for each item

I pretty much immediately wrote off the checkboxes as helper cells and created a hidden query to dump everything that was marked true in what I'm going to call Range_Items (Though I'm really just referencing the cells they were dumped in).

I haven't tried limiting the final query against the date range yet, because I'm having trouble making it pull from Sheet1 against the Range_Items list. So I tried paring it down to a simpler formula for concept and still can't seem to figure out why it won't pull anything. This is the simplest iteration I can think of:

=Query('Transaction Dump'!$A:$J,"select B,C,E,F,G,I where C = 'Expense'")

But when I try C = Or('Expense', 'Transfer') using cell reference or lack of single quotes, I get hit with errors.

I'm sure the problem is the syntax, but I really can't tell what's stopping it.

Some other attempts I've tried include:

  • Simply referencing the cell range in the OR function
  • Nesting another query into the query
  • =TEXTJOIN(",C =",True,$O$41:$O$57) where I then prefix that statement with =arrayformula("C = " & $C$59) since the first list item would not have the C = operator and stick that into the Query's Or function.

I've tried a billion combinations of single, double, and & insertions. Any help would be greatly appreciated.

Best Answer

  • try it like this and if you will need in future the and you just replace the or: 
    =QUERY('Transaction Dump'!$A:$J, 
 "select B,C,E,F,G,I 
  where C = 'Expense'
     or C = 'Transfer'", 0)
  • also you may change = to matches or contains

_______________________________________________________________

cell B59: 

="contains '"&TEXTJOIN("' or Col3 contains '", 1, $O$41:$O$57)&"'"

cell Y3: 

=QUERY({'Transaction Dump'!$A7:$J}, 
 "select Col2,Col3,Col5,Col6,Col7,Col9 where Col3 "&C59, 0)

0

Related Topic