Google Sheets FILTER with “IN” Logic: List Has Value from Master List

5 min readMay 5, 2024

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.

You can give it a try using this Sample Sheet

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 with ColN to refer to the Nth 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 range D7:I17 based on a provided condition.
REGEXMATCH(H7:H17,…): The condition checked by the filter is whether there’s a match in the range H7:H17.
JOIN(“|”,B26:B28): This part creates a single text string from the values in cells B26 to B28, 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 as TRUE and #N/A as FALSE.
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.