Google Sheets now allows selecting multiple values in drop-downs, making it easier to filter data dynamically. However, there’s a challenge—selected values are stored as comma-separated text, making filtering difficult with standard formulas.

This tutorial explains how to filter data using a multi-select drop-down in Google Sheets. That means you will filter a column based on multiple criteria selected in a drop-down menu using an efficient formula.

Challenges in Filtering Multi-Select Drop-Down Data

The issue with filtering multi-select drop-down data is that the selected criteria appear as drop-down chips, but in reality, they are stored as comma-separated values. To use them as criteria in the FILTER function, we need a different approach.

Formula to Filter Data Using a Multi-Select Drop-Down

Use the following generic formula:

=FILTER(range, XMATCH(criteria_range, SPLIT(criteria, ", ", FALSE)))

Where:

• range – The range of data to filter.
• criteria_range – The column to evaluate.
• criteria – The cell containing the multi-select drop-down.

Example: Filtering Data Using a Multi-Select Drop-Down

Let’s consider the following sample data in A1:B, where column A contains items and column B contains their colors:

You want to filter the items by colors, such as all items in black color or all items in black or red color. To achieve this:

Step 1: Create a Multi-Select Drop-Down

1. Navigate to cell D1.
2. Click Insert > Drop-down.
3. In the sidebar, select ‘Drop-down (from a range)’.
4. Click the ‘Select data range’ icon and enter B2:B in the pop-up.
5. Click OK, then check “Allow multiple selections”.
6. Click Done.

Now, cell D1 contains a multi-select drop-down based on the unique values in column B.

Step 2: Apply the FILTER Formula

Enter the following formula in cell E2:

=FILTER(A2:B, XMATCH(B2:B, SPLIT(D1, ", ", FALSE)))

How This Formula Works:

1. SPLIT(D1, ", ", FALSE) – Splits the multi-select drop-down values into an array.
2. XMATCH(B2:B, ...) – Matches each value in column B against the split array.
3. FILTER(A2:B, ...) – Filters rows where a match is found.

Expected Output:

If you select Black, Red in cell D1, the filtered result will be:

Key Takeaways

• Multi-select drop-downs store values as comma-separated text.
• The SPLIT function helps convert them into an array for filtering.
• The XMATCH function efficiently finds matches.

Related Resources

• Filter Rows Containing Multiple Selected Values in Google Sheets
• Multiple Selection Dependent Drop-Downs in Google Sheets
• Auto-Populate Information Based on Drop-down Selection
• Create a Drop-Down to Filter Data From Rows and Columns