In this article:

Multiple Dependent Drop-down list in Google Sheets (2024 Update)

May 8, 2024

What is a Multiple Dependent Drop-down list in Google Sheets?

A multiple dependent drop-down list in Google Sheets enables interconnected drop-down menus where selections in one list determine options in the next. This functionality is useful for organizing and managing data to allow a more precise and relevant data entry or filtering.

Example: Selecting a 'Genre' in the first drop-down can dictate the available 'Authors' in the next drop-down linked to that specific genre.

How to Create a Multiple Dependent Drop-down list in Google Sheets

Creating a multiple dependent drop-down list in Google Sheets is helpful for organizing and filtering data. In our example below, we’ll show you how to sort books by various categories and how you can utilize the multiple dependent drop-down lists feature. Simply follow the steps below. 

1. Enter Data in Your Spreadsheet

Populate your sheet with the categories ('Genre', 'Author', 'Year', 'Publisher') and their corresponding options. Place them in columns A, B, C, and D, starting from row 2 through row 5.

multiple dependent drop down list google sheets

2. Set Up Data Validation for Category Selection Across Rows

Navigate to cell A10, which will be your category selection cell, starting with 'Genre'. Select 'Data', then 'Data validation'.

Choose 'Add rule' within 'Data validation rules.'

Opt for 'Drop-down (from a range)' and input A1:D1 as your category range. Replicate this process for cells A11, A12, and A13.

3. Set Up Data Validation for Dependent Choices Across Rows

Select cell B10 and set up data validation. This is where you'll view options relative to your selection in A10.

Implement data validation here, but leave the range undefined for now as it will vary depending on the A10 selection. Apply the same to cells B11, B12, and B13.

4. Incorporate the Multi-Row Formula

This formula ensures appropriate options appear in cells B10 to B13 based on selections in A10 to A13. Choose a range for the formula, such as E2:H5. In E2, use:

=ARRAYFORMULA(IF(LEN(A10:A13), TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A13)=A1, INDIRECT("Genre"), IF(TRANSPOSE(A10:A13)=B1, INDIRECT("Author"), IF(TRANSPOSE(A10:A13)=C1, INDIRECT("Year"), INDIRECT("Publisher")))))), ""))

Alternatively, apply this template in E2 or your selected range:

=ARRAYFORMULA(IF(LEN(A10:A13), TRANSPOSE(ARRAYFORMULA(IF(TRANSPOSE(A10:A13)=[Category1 Cell], INDIRECT("[Named Range1]"), IF(TRANSPOSE(A10:A13)=[Category2 Cell], INDIRECT("[Named Range2]"), ...)))), ""))

Replace [Category1 Cell] and [Named Range1] with the respective category header cell and named range, and do so for each category.

5. Connecting Dependent Drop-downs to the Formula

To modify the data validation for B10, click the cell 'B10,' then the edit icon.

Select 'Drop-down (from a range)' and link it to E2:H2.

Follow this for B11 (E3:H3), B12 (E4:H4), and B13 (E5:H5). These ranges will adjust based on your A10 to A13 choices.

6. Test Your Configuration

To ensure everything is set up correctly, choose 'Genre' in A10 and observe if B10 displays the correct genre options.

Repeat this for cells A11, A12, and A13 and check the corresponding B cells for accurate options.

We hope that you now have a better understanding of how to create a multiple dependent drop-down list in Google Sheets. If you enjoyed this article, you might also like our article on how to create a dependent drop-down list in Google Sheets or our article on how to Zoom in on Google Sheets.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->