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.

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.

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)

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

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#

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,"")

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,"")))

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 😀👍