title: Google Sheets Filter with IN clause
body: <p><strong>Question:</strong> In Google Sheets, using <strong><em>only</em></strong> the built-in functions, how do I write a filter to exclude records based on a column in each row not being present in a list of valid values from another range.</p>
  
  <p><strong>Details</strong></p>
  
  <p>I am using Google Sheets to write a finance spreadsheet where all of my expenses and incomes are entered into a single sheet called Transactions. I have a separate sheet called Constants where I keep a list of income categories and a list of expense categories. </p>
  
  <p>Here is some sample data for purposes of the question:</p>
  
  <p><em>Constants</em></p>
  
  <p><a href="https://i.stack.imgur.com/WuewA.png" rel="noreferrer"><img src="https://i.stack.imgur.com/WuewA.png" alt="enter image description here"></a></p>
  
  <p><em>Transactions</em></p>
  
  <p><a href="https://i.stack.imgur.com/MBfQ4.png" rel="noreferrer"><img src="https://i.stack.imgur.com/MBfQ4.png" alt="enter image description here"></a></p>
  
  <p>I have a sheet called ByMonth where I want to show all of the expense transactions in one section and all of the income transactions in a separate section. 
  Goal:</p>
  
  <p><a href="https://i.stack.imgur.com/H17YH.png" rel="noreferrer"><img src="https://i.stack.imgur.com/H17YH.png" alt="enter image description here"></a></p>
  
  <p>I need to essentially do this sql query in google sheets functions:  </p>
  
  <pre><code>select date, category, amount from transactions 
  where category not in (
      select * from expense_categories
  )
  </code></pre>
  
  <p>but I cannot figure out how to get Google Sheets to let me do an IN concept with their functions.</p>
  
  <p>Here is a cell function expression that I am using for filtering the rows based on a date field:</p>
  
  <pre><code>=filter(Transactions!A2:C, 
          DATEVALUE(Transactions!A2:A) &gt;= date(2015,4,1), 
          DATEVALUE(Transactions!A2:A) &lt;= date(2015,4,30)
  )
  </code></pre>
  
  <p>I would be adding a third condition to the filter() function, and that third condition would somehow compare the Category column of each row of data against the list of valid expense or income categories and return a boolean.</p>
  
  <p>Here are some other things that I have tried, to no avail, including some variations of MATCH, ARRAYFORMULA, etc:</p>
  
  <pre><code>=filter(Transactions!A2:C, row(Transactions!B2:B) = UNIQUE(Constants!A2:A))
  =filter(Transactions!A2:C, Transactions!B2:B = UNIQUE(Constants!A2:A))
  =filter(Transactions!A2:C, Transactions!B2:B = Constants!A2:A)
  </code></pre>
  
  <p>Any suggestions?</p>