If you haven’t had a chance to read about Dependent Dropdown lists in Google Sheets, take a moment to click the image below to read (or watch) a complete walkthrough.

Setting Up the Supporting Data

We have a spreadsheet where the first dropdown list will be created in cell A3 while the dependent (second) dropdown list will be created in cell B3.

The lists for the two dropdowns are located on a sheet named “Master” where the first list points to the heading row of the table (cells A3:C3) and the second list points to the remaining items in the table (cells A4:C20).  A few extra rows were selected to accommodate additions to the table.

Featured Course

Google Sheets – The Comprehensive Masterclass

Master the Powerful Features of Google Sheets from data collection, automation to proper data analysis and visualization. This course will teach you everything you need to know about Google Sheets. Plus you’ll benefit from our EXCLUSIVE bonuses.
Learn More
Google Sheets course cover

Creating the First Dropdown List

To create the dropdown for Division, perform the following steps:

  1. Select cell A3.
  2. Select Data -> Data Validation.
  3. In the Data Validation dialog box, select “List from a range” (now called “Dropdown (from a range)”) in the Criteria section and define the range as Master!A3:C3. We also wish to disallow any custom data from being entered into the cell, so we activate the “Reject input” option.

We can click the dropdown button located in cell A3 to reveal the following list of Divisions.

When we copy cell A3 and paste it to range A4:A12, we see that the Data Validation logic is replicated across all pasted cells.

Google Sheets Update

Please note that Google Sheets has updated the Data Validation experience since the video was recorded. Instead of a pop-up window, you get a pane on the right-hand side. The default style of a dropdown is now a “chip”, but you can change it to the standard “arrow”, like you see in the video.

And crucially, the dollar signs that apply the absolute reference to dropdown ranges are no longer implied. You see them and can edit them like you would in Excel.

Data Validation pane in Google Sheets with "Dropdown from a range" selected as criteria and =Master!$A$3:$C$3 as the range.
Data Validation pane in Google Sheets (as of Jan 2024)

Setting Up the Second (Dependent) Dropdown List

Now we create dropdown lists that are reactive to the first dropdown lists.  These must work for each row independently of the adjacent rows.

Remember we only wish to display Apps that are associated with the selected Division from the first dropdown list.

Creating the Data Preparation Area

As Google Sheets will not allow us to create a formula directly in the cell where the dropdown is to exist, we will write the formula in a “helper column” and reference the results with Data Validation.

Creating an Index List

Returning to the “Master” sheet, we will create a list of numbers from 1 to 10 in cells E4:E13.  Each of these numbers corresponds to a “first” dropdown on the input sheet.

Creating the App Lookup List

To create a list of Apps that correspond to the user’s selection in the first dropdown (cell A3 – input sheet), select cell F4, and enter the following formula.

=INDEX($A$4:$C$20, , MATCH('Dep. List'!A3, $A$3:$C$3, 0) )

The result is a list of Apps from the selected Division.

To break the formula down into its pieces:

  1. The INDEX function uses the first argument to select the array of cells $A$4:$C$20.
  2. We want ALL ROWS to be returned, so we omit the second argument in the INDEX function, hence the 2 commas with nothing between them.
  3. The third argument uses a MATCH function to locate the item listed in cell A3 of the input sheet within the list of Department in cells $A$3:$C$3. This match must be an exact match, so a 0 is used to set this behavior in the MATCH.

Transposing the Results

Because we want our results to be listed horizontally (across the row) instead of vertically (down the column), we will nest the entire INDEX/MATCH formula within a TRANSPOSE function.

=TRANSPOSE(INDEX($A$4:$C$20, , MATCH('Dep. List'!A3, $A$3:$C$3, 0) ) )

Filling Down for the Remaining Lists

If we take the formula created in cell F4 and fill it down to cell F13, we are presented with a panic moment.

Not to worry.  This is only because we have not selected any Divisions from the remaining dropdowns.  These errors will disappear once we occupy the remaining division dropdowns.

But who wants to look at errors that aren’t real errors?

To suppress the #N/A error messages, we will nest the entire TRANSPOSE/INDEX/MATCH formula within an IFNA() function.

=IFNA(TRANSPOSE(INDEX($A$4:$C$20, , MATCH('Dep. List'!A3, $A$3:$C$3, 0) ) ) )

If we replace the original TRANSPOSE formulas with the updated formula, we see the following results.

Copying the Dependent Dropdown to More Rows

We return to the input sheet to set up the dependent dropdown list for the Apps.

To create the dropdown for Division, perform the following steps:

  1. Select cell B3.
  2. Select Data -> Data Validation.
  3. In the Data Validation dialog box, select “List from a range” (“Dropdown (from a range)”) in the Criteria section and define the range as Master!$F$4:$Z$4. We also wish to disallow any custom data from being entered into the cell, so we activate the  “Reject input” option.  A few extra columns were selected to accommodate additions to the table.
Data validation pane in Google Sheets with absolute referencing applied to the entire criteria range.
Data validation with absolute referencing in the criteria range

The result is a list of Apps for the selected Division.

If we copy the B3 cell and paste it across the remaining Column B cells, we don’t get what we were expecting from the dependent dropdowns.

Every dependent dropdown has the list for whatever Division is selected in cell A3.  This is because all dependent dropdown lists are looking at range F4:Z4 for their list of Apps.

To solve this, you must edit the criteria range applied to cell B3 before you copy the data validation down.

Since we’re copying the cells across rows rather than columns, all we need to do is remove the dollar sign from the row number. So, instead of =Master!$F$4:$Z$4 we should have =Master!$F4:$Z4.

Data validation pane in Google Sheets with mixed referencing applied to the criteria range (fixed for columns and relative for rows).
Data validation with mixed referencing in the criteria range (relative row)

Now, when you copy the data validation cell down column B, the row references to the data preparation range shift as well and match the selection in the department dropdown.

Practice File

Feel free to get your own copy of the file HERE.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.