Drop down list with multiple dependent and if statement

The tutorial shows how to create an Excel drop down list depending on another cell by using new dynamic array functions.

Creating a simple drop down list in Excel is easy. Making a multi-level cascading drop-down has always been a challenge. The above linked tutorial describes four different approaches, each including a crazy number of steps, a bunch of different formulas, and a handful of limitations relating to multi-word entries, blank cells, etc.

That was the bad news. The good news is that those methods were designed for pre-dynamic versions of Excel. The introduction of dynamic arrays in Excel 365 has changed everything! With new dynamic array functions, creating a multiple dependent drop-down list is a matter of minutes, if not seconds. No tricks, no caveats, no nonsense. Only fast, straightforward and easy-to-follow solutions.

• Make a dynamic drop down list in Excel
• Create a multiple dependent drop down list
• Create an expandable dropdown excluding blank cells
• Sort drop down list alphabetically

How to Create a Dependent Drop Down List in Excel

FREE EXCEL TIPS EBOOK – Click here to get your copy

Watch Video – Creating a Dependent Drop Down List in Excel

An Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards.

It shows a list of items as a drop down in a cell, and the user can make a selectionfrom the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells.

Below is an example of an Excel drop down list:

1. Select List from the Allow drop-down box, and then type the following formula:

The formula uses 2 range names – ShowFruit and ShowMeat. Depending on the option chosen, the corresponding list will show up in the drop-down list.

1. Click
   OK.
2. Click on the drop-down list to see the list of available fruit.

1. Click on Show Meat, and then click on the drop-down list again. The list will have changed to show the meat list while the top value will have change to “Show Fruit”.

Example for Creating Multiple Dependent Drop-Down Lists

Let’s take a look at the below data for which you intend to create a dependent drop-down list using the offset formula.

Here you can see three different leagues, each with its list of teams. In order to simplify the concept, each league can only have a small number of teams instead of featuring a full list.

On the left-hand side, you have two choices to select a league and its corresponding team. Your team selection will depend on what league you choose, as choice two is dependent on the first choice.

Our goal is to create a simple drop-down menu for league names and a dependent drop-down menu for each league’s list.

1.
Go to the
Data tab
and click on
Data Validation.

2.
Select the
List in Allow
option in validation criteria.

3.
Select cells
E4 to G4
as the source.

4.
Click
OK
to apply the changes.

In three easy steps, you can create a simple drop-down list. Then
copy and paste
the formula to the rest of the cells down the row.