A simple drop-down list is easy to create and helps user to select a value from a pre-defined list. But it’s a challenge to create a dependent drop-down list, a second list which changes with the value selected from the first list.

In this tutorial, we will learn how to create a dependent drop-down list.

Step 1: We have created a drop-down list which contains all the department.

What if we want to create a dependent drop-down list, a second list which changes with the value selected from the first list. We need to add another list which contains the employee’s name.

Step 2: We need to write an Index and Match formula to get the correct second list from the value selected from the first list. We can’t select more than one column or row for the data validation list.

Step 6: Copy this Index and Match formula to make the second list dynamic. Go to Data validation and add another list. Under source, paste the formula.

Step 7: A second dependent list is created which changes after selecting the value from the first list.

It’s helpful to have a dependent drop-down list, but the second list have some blanks.

The contents of a drop-down lists are continuously changing, we want to have a dynamic drop-down list that can expand and exclude any blank cells itself.