Google Sheets Filter with IN clause

Question

Question: In Google Sheets, using only 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.

Details

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.

Here is some sample data for purposes of the question:

Constants

Transactions

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:

I need to essentially do this sql query in google sheets functions:

select date, category, amount from transactions 
where category not in (
    select * from expense_categories
)

but I cannot figure out how to get Google Sheets to let me do an IN concept with their functions.

Here is a cell function expression that I am using for filtering the rows based on a date field:

=filter(Transactions!A2:C, 
        DATEVALUE(Transactions!A2:A) >= date(2015,4,1), 
        DATEVALUE(Transactions!A2:A) <= date(2015,4,30)
)

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.

Here are some other things that I have tried, to no avail, including some variations of MATCH, ARRAYFORMULA, etc:

=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)

Any suggestions?

Answer 1

The formula you are interested in is:

=filter(Journal!A2:C13,IFERROR(Match(Journal!B2:B13,Categories!A2:A3,0)))

The function match(a, b, 0) searches if a can be found in b. You need 0 at the end for exact match (default is closest match). If a cannot be found, you get an error, so you wrap the function with iferror to skip error.

Here is working example file

Answer 2

As each category use a keyword, expense or income, the following formulas return the desired results

=query(Journal!A2:C13,"Select * where B contains 'expense'")
=query(Journal!A2:C13,"Select * where B contains 'income'")