Dynamic Arrays Tips and Tricks

Depending drop-down list

Reading Time: 2 minutes

This article will explain how to create drop-down list depending on each other from a table without extracting first

Table with hierarchical data

We will start from a table where the data is arranged in a hierarchical way. The data does not need to be sorted.

Hierarchical data

With this data, we will build a depending drop-down list. You can see in this picture that only the beverage sub-category is enabled in the drop-down list.

Presentation of a depending drop down list

To achieve this work, we will use the dynamic array functions that are present in Microsoft 365 or Excel Online only.

Creation of the first drop-down menu

For the first drop-down list, the one corresponding to the highest hierarchical level, we will extract the unique values ​​of the column A. For this, we will use the SINGLE function.

=UNIQUE(A2:A56)

UNIQUE function to extract unique category

And we can also embed this formula into the function SORT to order the list of products

SORT function to reorder the result

Next, we will create a drop-down list in the menu Data> Data validation

Choose the List option and write the following function

=$E$2#

Drop down list with dynamic array references

The # sign corresponds to the extended reference of the array formula.

With this trick, whatever the number of values returned by the array formula UNIQUE, the drop-down list will always displayed all the values.

Create the dependent drop-down list

Now we're going to build a new formula based on the result of the previous selection. This time, we will use the FILTER function.

Because the FILTER function extract values in function of a condition. The condition will be the category selected 😉

To write the formula, we will

  • Indicate the column that contains the data to return
  • Indicate the column on which to filter
  • the test to perform (here =)
  • the cell containing the test value (here cell I3)

=FILTER(B2:B56,A2:A56=I3,"")

FILTER function to create the dependent drop down list

Of course, we only need to keep unique values. And again we will use the UNIQUE function

=UNIQUE(FILTER(B2:B56,A2:A56=I3,""))

And to make things perfect, we will also sort the data

=SORT(UNIQUE(FILTER(B2:B56,A2:A56=I3,"")))

Depending list with the FILTER function

And as we did for the first drop-down menu, we'll again use the extended references to design the second drop-down menu with the result of the second formula.

=$F$2#

And with this new function we have created a depending drop-down list 😀👍

Dependant Drop Down list

Related posts

UNIQUE Function – Remove duplicates with formulas

Frédéric LE GUEN

Dynamic filter in Excel

Frédéric LE GUEN

Calculation without equal

Frédéric LE GUEN

Leave a Comment