Data validation using if statement

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.

1) Stay in the Settings tab; 2) Select List in the Allow box;

3) In the Source box, select the range of cells containing the values you want to display in the drop-down list (here I select the headers of the table)

4) Click the OK button. See screenshot:

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.

1) Stay in the Settings tab; 2) Select List in the Allow drop-down list; 3) Enter the following formula in the Source box; 
=IF($E$2=$B$2,$B$3:$B$6,$C$3:$C$6)
4) Click the OK button. See screenshot:

Note: This formula tells Excel: If the value in E2 is equal to the value in B2, display all values in the range B3:B6. Otherwise, display the values in the range C3:C6.

1) E2 is the drop-down list cell you specified in step 1 that contains headers. 2) B2 is the first header cell of the original range. 3) B3:B6 contains the cities in United States. 4) C3:C6 contains the cities in France.
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.

Note:

1) The above method only works for two countries and their cities, because an IF function is used to test a condition and return one value if the condition is met, and another value if it is not met.

2) If more countries and cities are added to this case, the following nested IF functions and the IFS functions can help.

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:

1) Stay in the Settings tab; 2) Select List in the Allow drop-down list; 3) Select the range containing the countries in the Source box; 4) Click the OK button. See screenshot:

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.

Notes:

1) This step enables you to create multiple named ranges simultaneously. Here the row headers are used as the range names.

2) By default, the Name Manager does not allow spaces when defining new names. If there are spaces in the header, Excel will convert them to a (_) instead. For example, United States will be named United_States. These range names will be used in the following formula.

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:

1) Stay in the Settings tab; 2) Select List in the Allow drop-down list; 3) Enter the following formula in the Source box; 
=IF($E$10="Japan",Japan,IF(E10="Tunisia",Tunisia,IF(E10="United States",United_States, France)))
4) Click the OK button.

Note:

If you are using Excel 2019 or later versions, you can apply the IFS function to evaluate multiple conditions, which does the same thing as nested IF, but in a clearer way. In this case, you can try the following IFS formula to achieve the same result.

=IFS(E10="Japan",Japan,E10="Tunisia",Tunisia,E10="United States",United_States,E10="France", France)
In the above two formulas, 1) E10 is the drop-down list cell containing the countries you specified in step 1;

2) The texts in double quotes stand for the values you will select in E10, and the texts without double quotes are the range names you specified in Step 2;

3) The first IF statement IF($E$10="Japan",Japan) tells Excel:

If E10 is equal to “Japan”, then only the values in the named range “Japan” are displayed in this drop-down list. The second and the third IF statements mean the same thing.

4) The last IF statement IF(E10="United States",United_States, France) tells Excel:

If E10 is equal to “United States”, then only the values in the named range “United_States” are displayed in this drop-down list. Otherwise, it displays the values in the named range “France”.

5) You can add more IF statements to the formula if you need. 6) Click to know more about the Excel IF function and the IFS function.
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:

1. In the dialog box, choose Mode A: 2 Levels in the Mode section; 2. Select the columns you need to create conditional drop-down list based on; 3. Select an output range. 4. Click OK. Note: 1) Kutools for Excel offers a 30-day free trial with no limitations, go to download.

2) In addition to creating a 2-level drop-down list, you can easily create a 3 to 5-level drop-down list with this feature. Take a look at this tutorial: Quickly create multiple levels drop-down list in Excel.

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:

1) Stay in the Settings tab; 2) Select List in the Allow drop-down list; 3) Enter the following formula in the Source box; 
=INDIRECT(SUBSTITUTE(E10," ","_"))
4) Click the OK button.

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

Related Articles

Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.

Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.

Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.

Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.