Filter Data with Multi-Select Drop-Downs in Google Sheets

If you’ve started using multi-select drop-downs in Google Sheets, you may encounter challenges when using them as criteria for filtering data. In this tutorial, we’ll explore how to filter data using multi-select drop-downs in two different ways.

In the first example, we’ll use a multi-select drop-down to evaluate values in a standard column of data. This is straightforward and easy to implement.

In the second example, we’ll tackle a more complex scenario: evaluating criteria in a column that also contains multi-select drop-downs. This approach is more challenging as it involves using a LAMBDA function. But don’t worry—we’ll simplify it with clear, step-by-step instructions.

Example 1: Filter Regular Column with Multi-Select Drop-Down

We’ll use basic sample data—such as fruit items—to make it easier to grasp. The following sample data is in columns A and B:

Date	Item Bought
1/12/24	Apple
1/12/24	Orange
1/12/24	Mango
5/12/24	Orange
10/12/24	Banana
10/12/24	Pear

The multi-select drop-down is in cell D1, where you can choose multiple items from column B.

Next, we will filter columns A and B based on the multi-select drop-down in D1. If you’re not familiar with creating this drop-down, please check out Mastering Multiple-Selection Drop-Down Chips in Google Sheets.

To filter the data, use the following formula:

=FILTER(A2:B, REGEXMATCH(B2:B, "\b("&SUBSTITUTE(D1,", ", "|")&")\b"))

Example of filtering a regular column with a multi-select drop-down in Google Sheets

Formula Breakdown for Example 1

The FILTER function returns rows where the condition evaluates to TRUE.
The condition is REGEXMATCH(B2:B, "\b("&SUBSTITUTE(D1,", ", "|")&")\b").
- The REGEXMATCH function checks for the pattern "\b("&SUBSTITUTE(D1,", ", "|")&")\b" in column B.
- The SUBSTITUTE function replaces “, ” (comma followed by space) with “|” (pipe) to create a pattern match for multiple items. Since the values in the multi-select drop-down are separated by commas, the pipe (|) operator allows the formula to match any of the items listed in column B.
- We use \b on both sides to specify word boundaries; otherwise, “apple” could match “applepie.”

Example 2: Filter Multi-Select Column with Multi-Select Drop-Down

This example involves a more complex scenario, though it’s rare in real-life situations.

Assume you have the following data in columns A and B:

Date	Item Bought
1/12/24	Apple, Orange, Mango
5/12/24	Orange
10/12/24	Banana, Pear
14/12/24	Apple, Mango
15/12/24	Apple, Orange

How do you filter the rows that match the multi-select drop-down criteria in cell D1 in column B? You want to filter only the rows where all criteria in D1 match the values in column B.

For example, if D1 contains “Apple, Mango”, the formula should filter the second and fifth rows.

To achieve this, use the following formula:

=LET(
   dataCol, ARRAYFORMULA(SPLIT(B2:B, ", ", FALSE)), 
   criteria, SPLIT(D1, ", ",FALSE), 
   FILTER(A2:B, BYROW(dataCol, LAMBDA(row, COUNT(XMATCH(criteria, row))))=COUNTA(criteria))
)

Example of filtering a multi-select column with a multi-select drop-down in Google Sheets

Formula Breakdown for Example 2

ARRAYFORMULA(SPLIT(B2:B, ", ", FALSE)) splits the multi-select drop-down values in column B. This is assigned the name dataCol using LET.
SPLIT(D1, ", ", FALSE) splits the multi-select drop-down criteria in cell D1, and is assigned the name criteria.

The LET function helps improve formula efficiency by allowing you to reuse expressions without recalculating them multiple times.

BYROW(dataCol, LAMBDA(row, COUNT(XMATCH(criteria, row)))) = COUNTA(criteria) checks if the number of matches (for each row in dataCol) is equal to the number of criteria in D1. If this condition is met, the formula returns TRUE.
The FILTER function then filters the rows in A2:B based on the condition being TRUE.

Summarize Data and Keep the Last Record in Google Sheets