Trying to Query a list against another list

by Ryan Martinez   Last Updated May 15, 2019 13:03 PM - source

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 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.

Related Questions

Import range from 3 sheets/tabs into 4th

Updated February 06, 2019 12:03 PM