Google Sheets FILTER with “IN” Logic: List Has Value from Master List
Hello, this is Max from Cooltables.
Imagine managing a business with an expansive product inventory on Google Sheets. Suddenly, you need to organize a special sale involving select products. The challenge: efficiently filtering these specific items from your vast inventory, rather than manual searching.
You compile the sale items on a separate list, then devise a system that automatically sifts through your inventory. If it identifies a product on your sale list, it instantly fetches all related details.
You’ll see here four different solutions for filtering your product list. Each has been tested for speed, allowing you to choose the fastest and most suitable approach for your needs. Let’s get started!
>>>Sample Spreadsheet<<<
Benchmark
The picture below shows the speed test.
We conducted three tests using data sets of 10K rows each. The first test was finding 3 values among 5 unique ones, the second involved searching for 9 values out of 20, and for the third, we looked for 500 values among 1000 unique data points.
The methods used were:
- `CONDITION_ONE_OF_RANGE` a hidden Google Sheets function
- `QUERY` with regex inside
- `REGEXMATCH`
- `MATCH`
The results were:
- Test 1: `REGEXMATCH` was the fastest, though all methods performed quickly.
- Test 2: `CONDITION_ONE_OF_RANGE` was the winner, all methods were swift.
- Test 3: `REGEXMATCH` had a surprisingly quick result, while `CONDITION_ONE_OF_RANGE` was much slower than others.
All tests were made with the help of the BENCHMARK named function. The documentation on this function is in our “Google Sheets Tips” community:
CONDITION_ONE_OF_RANGE
Filter list in a list with Google Sheets CONDITION_ONE_OF_RANGE
Formula.
=FILTER(D7:I17,CONDITION_ONE_OF_RANGE(H7:H17,B26:B28))
Formula breakdown
1. D7:I17
: This is the range of cells from which you want to filter data. This can be customized according to your spreadsheet.
2. CONDITION_ONE_OF_RANGE(H7:H17, B26:B28)
: Here, H7:H17
is the range that you want to evaluate.
B26:B28
is the master list or condition range, against which the cells in the column H7:H17
are compared.
If any cell in the range H7:H17
contains one of the values from the range B26:B28
, the function FILTER()
will return the entire row (from D7
to I17
) where the match is found.
CONDITION_ONE_OF_RANGE
is a hidden function, similar to SQL IN
clause in a database system, which is particularly handy when a condition is expected to match any of several possible values. The IN clause in SQL is used to reduce the use of multiple OR
conditions.
You can explore other hidden Google Sheets functions, see how in this tip.
`QUERY
` with regex inside
Query Rows Based on List Matching in Google Sheets
=QUERY(D7:I17, “where Col5 matches(‘“&JOIN(“|”,B26:B28)&”’)”)
Function Breakdown
D7:I17
is the data range to be queried.Col5
refers to the fifth column in the given data range. Replace this withColN
to refer to theN
th column in your data range.- The
matches
keyword compares column values to the pattern provided by the regular expression. JOIN(“|”,B26:B28)
uses the `JOIN` function to create a string, separated by a “|
”, from the values in cells B26 to B28. This forms the regular expression.
This function returns all the rows in the range D7:I17
where the fifth column’s value matches any of the values in the range B26:B28
. The result is similar to the SQL “IN
” clause.
The vertical bar “|
”, as used in the `JOIN` function, is the regular expression symbol for logical OR
, forming a regular expression that matches any of the values in the range B26:B28
.
Modify the data range and column number as per requirement. Ensure that the cells used in JOIN
function contains the potential values that you’re searching for in the designated search column.
Caution
If the string expression generated by JOIN(“|”,B26:B28)
exceeds 50,000 characters, this will throw an error.
REGEXMATCH with “OR” logic
Return all rows from D7
to I17
where the value in column H matches any of the values in the cell range B26
to B28
=FILTER(D7:I17,REGEXMATCH(H7:H17,JOIN(“|”,B26:B28)))
Function Breakdown
FILTER(D7:I17,…)
: Filters the rangeD7:I17
based on a provided condition.REGEXMATCH(H7:H17,…)
: The condition checked by the filter is whether there’s a match in the rangeH7:H17
.JOIN(“|”,B26:B28)
: This part creates a single text string from the values in cellsB26
toB28
, with each value separated by the “|
” character. In regular expression language, “|
” stands for “OR
”. This means that any value from those cells can satisfy the matching condition.
Limitations
- Google Sheets has a limit of 50,000 characters that can be used inside JOIN.
- Building regular expressions may be tricky as it has lots of special chars like dot “
.
” meaning any char match.
MATCH with “IN” logic
Filter rows based on list matching in Google Sheets:
=FILTER(D7:I17,MAP(H7:H17,LAMBDA(d,MATCH(d,B26:B28,))))
The function finds rows from D8:I17
where corresponding values from H7:H17
exist in B26:B28
and displays those rows.
MATCH(d,B26:B28,)
: ‘d
’ signifies each value in `products_column`H7:H17
. Each ‘d
’ is individually searched in `search_list`B26:B28
.LAMBDA(d,MATCH(d,search_list,))
: A temporary function makes ‘d’ represent each cell in `products_column`H7:H17
.MAP(H7:H17,LAMBDA(d,MATCH(d,B26:B28,)))
: The temporary function generates an array where each position notes a found match or #N/A if not found. The FILTER function reads index positions asTRUE
and#N/A
asFALSE
.FILTER(data,…)
: Filters the range `data` based on the “true/false” array from the `MAP` function, including only corresponding TRUE values.
Conclusion
For large projects, I’d suggest using the “MATCH” approach as it shows stable results for each test.
The “regex”-based solution showed great results, but I do not recommend using them if you are not sure you are well known with Regular Expressions
***
Join our Twitter community for more tips like this.