Filter Data Using a Multi-Select Drop-Down in Google Sheets

Published on

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.

Filter data using multi-select drop-down chips in Google Sheets for dynamic filtering

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:

ItemColor
ShirtBlack
JacketRed
PantsBlue
ShoesGreen
ScarfBlack
HatGrey
GlovesBrown
T-shirtYellow
SweaterTeal

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:

ItemColor
ShirtBlack
JacketRed
ScarfBlack

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.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Merge Duplicate Rows and Keep Latest Values in Excel

Here’s a dynamic array formula approach to merge duplicate rows and keep the latest...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.