Multiple-Selection Dependent Drop-Downs in Google Sheets

Published on

Google Sheets has introduced a new feature that allows users to select multiple values in drop-down lists, known as multiple selection drop-downs.

This tutorial explains how to create multiple-selection-dependent drop-downs (drop-down chips) in Google Sheets.

Can you explain the difference between a drop-down, multiple-selection drop-down, dependent drop-down, and multiple-selection dependent drop-down in Google Sheets?

  • Drop-down menu: A drop-down menu is a feature that allows you to select options from a predefined list in a cell. It uses data validation to limit input to specific options.
  • Multiple Selection Drop-down Menu: Similar to a drop-down menu, but allows you to select more than one value in a cell. The selected values will be displayed as chips.
  • Dependent Drop-down: A dependent drop-down consists of at least two drop-downs, where the options in the second menu depend on the selection made in the first menu. The items in the second menu change based on the items selected in the first menu.
  • Multiple Selection Dependent Drop-down: Similar to a dependent drop-down, but with the added functionality of selecting multiple values in the menus.

Let’s proceed with the steps, starting with a sample data set.

Step 1: Prepare the Sample Data

We will use a table with three columns, labeled Fruits, Vegetables, and Grocery in cells A1, B1, and C1, respectively.

Below these headers, you will list the relevant items: fruit names in A2:A, vegetable names in B2:B, and grocery items in C2:C.

Sample data for multiple selection dependent drop-down lists

Assume the sheet containing this data is named Sheet1.

In Sheet2, we will create two drop-down chips (menus).

Step 2: Create the Multiple-Selection Drop-Down

Navigate to cell A1 in Sheet2, where we will create the multiple-selection drop-down chip.

  1. Click Insert > Drop-down.
  2. In the sidebar panel that opens, select Drop-down (from a range) under “Criteria.”
  3. In the field below, enter Sheet1!A1:C1 to reference the column names from Sheet1.
  4. Check the box that says “Allow Multiple Selections.”
  5. Click Done to close the sidebar panel.

This will create a multiple-selection drop-down in cell A1.

Creating a multiple selection drop-down list in Google Sheets

Before we create the second drop-down that will depend on the multiple selections in the first drop-down, we need to add a formula in Sheet1. Let’s proceed to that step.

Step 3: Set Up the Helper Row for Multiple-Selection Dependent Drop-Downs

Enter the following formula in cell D2 in Sheet1:

=MAP(
   Sheet2!A1, 
   LAMBDA(
      choice, 
      IFNA(
         TOROW(
            FILTER(A2:C, XMATCH(A1:C1, SPLIT(choice, ", "))), 1
         ), "Waiting..."
      )
   )
)

Don’t worry about the complexity of the formula. If you follow the previous steps, this will work without any issues. For now, it will display the result “Waiting…”

Helper row formula for multiple selection dependent drop-downs

Within the formula:

  • Sheet2!A1 refers to the multiple selection drop-down chip in cell A1 in Sheet2.
  • A1:C1 refers to the column names in the current sheet (Sheet1), to the left of the formula.
  • A2:C refers to the data below the column names in the current sheet (Sheet1), to the left of the formula.

The formula filters the values in A2:C where A1:C1 matches the values selected in Sheet2!A1.

Step 4: Create the Multiple-Selection Dependent Drop-Down

Follow these steps:

  1. Navigate to cell B1 in “Sheet2.”
  2. Click Insert > Drop-down.
  3. In the sidebar panel, select Drop-down (from a range) under “Criteria.”
  4. Enter Sheet1!D2:2 in the field below.
  5. Check the box for “Allow Multiple Selections.”
  6. Click Done.

You have now created your first multiple-selection dependent drop-down list in Google Sheets. Let’s test it:

  • Select “Fruits” in cell A1.
    Cell B1 will show the available fruits to select.
  • Select “Fruits” and “Vegetables” in cell A1.
    Cell B1 will show the available fruits and vegetables.
Select items based on chosen categories

Step 5: Customize for Multiple Row Support

If you want the drop-down list to work across multiple rows, simply copying and pasting the menus from A1 and B1 to the rows below will not work correctly.

To enable multiple-row support for the multiple-selection dependent drop-down list, follow these steps:

  1. In the formula in “Sheet1,” replace Sheet2!A1 with Sheet2!A1:A.
  2. Navigate to cell B1 in “Sheet2” and click Insert > Drop-down again.
  3. Replace the reference =Sheet1!$D$2:$2 (absolute reference) in the sidebar panel with =Sheet1!D2:D (relative reference).
  4. Click Done.

Now, copy the drop-down chips from A1:B1 and paste them down as far as you need. Then, select the range A1:B and press Delete to clear any previously selected values. That’s all!

Here is my template for you to test the functionality:

Sample Sheet Download

Additional Notes

If you select “Fruits” and “Vegetables” in cell A1, the multiple-selection dependent drop-down in cell B1 will list all items under these categories. Once you select values in cell B1, any changes made to A1 will not automatically update B1. To refresh the options in B1, you need to delete its current values and reselect from A1.

The formula uses a LAMBDA function to support multiple rows for the multiple-selection dependent drop-down list. Be aware that LAMBDA functions can experience performance issues with very large datasets.

Resources

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.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.