If you need to create a drop-down list that changes based on what you select in another cell, adding a condition to the drop-down list can be a helper solution. When creating a conditional drop-down list, utilizing the IF statement is an intuitive method, as it is always used to test conditions in Excel. This tutorial demonstrates 5 methods that will assist you in creating a conditional drop-down list in Excel step-by-step.

Get Sample File:

Click to download the sample file

Use IF or IFS statement to create a conditional drop-down list

This section provides two functions: the IF function and the IFS function to help you create a conditional drop-down list based on other cells in Excel with two examples.

Add a single condition, like two countries and their cities

As shown in the gif below, you can easily switch between cities in two countries “United States and France” in the drop-down list. Let's see how to use an IF function to get it done.

Step 1: Create the main drop-down list

First, you need to create a main drop-down list that will serve as the basis for your conditional drop-down list.

1. Select a cell (E2 in this case) where you want to insert the main drop-down list. Go to the Data tab, select Data Validation.

2. In the Data Validation dialog box, follow these steps to configure the settings.

Step 2: Create a conditional drop-down list with an IF statement

1. Select the range of cells (In this case, E3:E6) where you want to insert the conditional drop-down list.

2. Go to the Data tab, select Data Validation.

3. In the Data Validation dialog box, you need to configure as follows.

=IF($E$2=$B$2,$B$3:$B$6,$C$3:$C$6)

Result

The conditional drop-down list is now complete.

As shown in the gif image below, if you want to select a city in United States, click on E2 to select Cities in United States from the drop-down list. Then select any city belonging to United States in the cells below E2. To select a city in France, do the same operation.

Add multiple conditions, like more than two countries and their cities

As shown in the gif image below, there are two tables. The one column table contains different countries, while the multi-column table contains cities in those countries. Here we need to create a conditional drop-down list that contains cities that will change according to the country you choose in E10, please follow the steps below to complete.

Step 1: Create a drop-down list containing all the countries

1. Select a cell (Here I select E10) where you want to display the country, go to the Data tab, click Data Validation.

2. In the Data Validation dialog box, you need to:

The drop-down list contains all countries is now complete.

Step 2: Name the cell range for the cities under each country

1. Select the entire range of the cities table, go to the Formulas tab, click Create from Selection.

2. In the Create Names from Selection dialog box, only check the Top row option and click the OK button.

Step 3: Create a conditional drop-down list

1. Select a cell (here I select E11) to output the conditional drop-down list, go to the Data tab, select Data Validation.

2. In the Data Validation dialog box, you need to:

=IF($E$10="Japan",Japan,IF(E10="Tunisia",Tunisia,IF(E10="United States",United_States, France)))

=IFS(E10="Japan",Japan,E10="Tunisia",Tunisia,E10="United States",United_States,E10="France", France)

Result

Just a few clicks to create a conditional drop-down list with Kutools for Excel

The above methods might be cumbersome for most Excel users. If you want a more effecient and straightforward solution, the Dynamic Drop-down List feature of Kutools for Excel is highly recommended to help you create a conditional drop-down list with just a few clicks.

As you can see, the whole operation can be done in just a few clicks. You just need to:

A better alternative to the IF function: the INDIRECT function

As an alternative to the IF and IFS functions, you can use a combination of the INDIRECT and SUBSTITUTE functions to create a conditional drop-down list, which is simpler than the formulas we provided above.

Take the same example used in the multiple conditions above (as shown in the gif image below). Here I will show you how to use the combination of the INDIRECT and SUBSTITUTE functions to create a conditional drop-down list in Excel.

1. In cell E10, create the main drop-down list containing all countries. Follow the above step 1.

2. Name the cell range for the cities under each country. Follow the above step 2.

3. Use the INDIRECT and SUBSTITUTE functions to create a conditional drop-down list.

Select a cell (E11 in this case) to output the conditional drop-down list, go to the Data tab, select Data Validation. In the Data Validation dialog box, you need to:

=INDIRECT(SUBSTITUTE(E10," ","_"))

You have now successfully created a conditional drop-down list using the INDIRECT and SUBSTITUTE functions.

Related Articles