Formula: Mimic SQL's "IN" and "NOT IN" Functions
Filter a range if a match is found within a list of options. Or don't. It's no skin off my tuchus
If you’ve used SQL, you’ve probably used “IN” and "NOT IN". Here’s how it works: if you want to filter a data set to include multiple values within a field, rather than having to type out “WHERE field = X OR field = Y OR field = Z” etc., you simply write “WHERE field IN (X,Y,Z)”.
“IN” should be a native function in Sheets. It's not. So here's a workaround:
FILTER ( returnRange , ISNUMBER ( MATCH ( criteriaRange , inclusionList , 0 ) ) )
Example 1: 2019 NBA 3PA Analysis
In this example, I use the formula to answer that age-old question: what was the average number of 3pt attempts by non-guards in the 2019 NBA season?
Here's the twist: NBA positions are often listed in combination: “SG-SF”; “PG-SG” etc etc. That makes the calc hard. There are many ways to tackle this, but in this example, I've addressed that twist by create an inclusions list - that is: filter the data if the position is found in the inclusion list (which, in this example, is: “C”; “PF; “C-PF”; “PF-C” “PF-SF”; and, “SF-PF”. Then, in the summary tab, in cell B9 I drop in the highlighted formula, filtering the multi-column data set (found in tab “data - 3pa_by_player”) for only those rows whose “position” is present in our “inclusion_list”.
(The answer is 89, by the way)
Example 2: S&P 500 Sector Analysis
In this second example, I've included a bit of added complexity. First, I've created an EXCLUSION list, rather than an INCLUSION list, so the formula filters out data that matches the criteria. This is the "NOT IN" formula. I do this by wrapping the ISNUMBER function within the formula with NOT (). Second, I've made the Exclusion List a comma-separated single value, rather than a list across multiple rows. To address this, I use the SPLIT () function. All together, the formula looks like this:
FILTER ( returnRange , NOT ( ISNUMBER ( MATCH ( criteriaRange , SPLIT ( inclusionList , delimeter ) , 0 ) ) ) ) )
Thank you to the 5 people who will ever read this post. Y'all the real MVPs.