Note: The other languages of the website are Google-translated. Back to English

Conditional Drop-Down List with IF Statement (5 Examples)

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 help you solve this problem. To create a conditional drop-down list, the first method that comes to mind is to use the IF statement, since it is always used to test for conditions in Excel. This tutorial demonstrates 5 methods to help you create a conditional drop-down list in Excel step-by-step.


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 from which you want to make a 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, you need to configure as follows.

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 I select 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.
Where
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 helps to quickly create multiple named ranges at once. 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 are troublesome for most Excel users. If you need an easier way, here the Dynamic Drop-down Listfeature 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

Instead of the IF and IFS functions, you can use a combination of the INDIRECT and SUBSTITUTE functions as another alternative option 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.

A conditional drop-down list with a combination of the INDIRECT and SUBSTITUTE functions is now created.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL