How to Use IF Statement to Create Drop-Down List in Excel

Frequently you need to create a conditional drop-down list. The IF function is a useful one while dealing with any “conditional” operation. In this tutorial, we are going to show you how to make a drop-down list using the IF statement in Excel.

How to Use IF Statement to Create Drop-Down List in Excel: 3 Methods

Here, we will show 3 effective methods to use the IF function to create a drop-down list in Excel. We will use direct cell reference and name range with the IF function. We will also show the use of IFS and INDIRECT to do the same task. Let’s get familiar with the dataset first.

Here we have listed three sports and a few famous players from the respective sporting fraternity. Using this data we will build the drop-down list with the help of the IF function.

Note that this is a simple dataset with a limited number of values (many famous players miss out) to keep things compact and straightforward. In a real-life scenario, you may encounter much larger and more complex data.

1. Insert IF Statement with Direct Cell Reference to Create Drop-Down List

Here, in our example will be such that, we will select a sports category as Favorite Sports, and depending on the sport, the player names will be shown as Favorite Player. This function is available in all versions of Excel. For that, select the following steps.

• Firstly, select Cell F7 where the sports type list should appear.

• Secondly, go to the Data tab and select Data Validation.

• Then, in the Data Validation window select List in Allow section and in Source section select the cell range $C$4:$E$4 as the sport types are in those cells.
• Further, press OK.

• Thus, the Favourite Sports list is created. You can select different sports from here.

• Afterward, select Cell F9 where we will create the drop-down list.
• Also, open the Data Validation window and write the following formula in the Source section and click on OK.

• Finally, we created the drop-down list.

• You can choose a different sport from Favorite Sports and will see the Favorite Player list updated accordingly.

Read More: Excel Formula Based on Drop-Down List

2. Generate Drop-Down List Using IF Statement with Name Ranges

In the previous method, we used direct cell reference to create the drop-down list. Now, we will replace the direct cell reference with Named Range. For that, follow the given steps.

• Firstly, create the drop-down list for Favorite Sports as we did before.

• Secondly, we will define the name range.
• To do that, select the cell range for Football Player.
• Then, go to the Formulas tab and select Define Name.

• Further, in the New Name window, give a Name for the cell range. We gave Football_Player as the name.
• Press OK.

• In a similar fashion, define the name range Cricket_Player and Tennis_Player for cricket and tennis player.
• Afterward, select Cell F9 as we want to create the drop-down list there.
• Later on, open the Data validation window as we did before.
• In the Data Validation window, select List in Allow section and write the following formula in the Source section and press OK.

• Finally, we have created the drop-down list. You can select a different sport from the Favorite Sports list and the Favorite Player list will update accordingly.

3. Apply IF Family Function to Create Drop-Down List in Excel

Instead of the IF function, we can use a family function of it: the IFS function. Since we need to check multiple logics, this IFS function will be useful then. This function is available in Excel 2019 or newer versions.

We don’t need to use multiple IF once we are using the IFS function. All we need to do is provide the logic and corresponding if_true value along with that. Follow the given procedures below.

• Firstly, create the Favorite Sports drop-down list as before.

• Then, select Cell F9 and open the Data Validation window.
• Afterward, select List in Allow section and write the following formula in the Source section.
• Consecutively, press OK.

• Finally, we will see the drop-down list created. You can select different sports from the Favorite Sports drop-down list and the Favorite Player drop-down list will update accordingly.

Read More: How to Create Dependent Drop Down List with Multiple Words in Excel

A Suitable Alternative of IF Statement to Create Drop-Down List in Excel

Instead of the IF function, we can use another alternative option to create a conditional drop-down list.

Let’s explore an alternative. We will create a conditional drop-down list using the INDIRECT function. This function is available in Excel 2007 or newer versions.

• Primarily, create the Favorite Sports drop-down list as we did before.

• Then, create the Name Range for players as earlier. But this time give the names as the list element in the Favorite Sports drop-down list i.e Football, Cricket and Tennis.

• Further, select Cell F9 to create a conditional drop-down list there.
• Also, open the Data Validation window.
• Afterward, select List in Allow section and write the following formula in Source section.
• Also, press OK.

• Finally, we will see the conditional drop-down list created.

Download Practice Workbook

You are welcome to download the practice workbook from the link below.

Conclusion

That’s all for today. We have listed several ways to build a drop-down list using the IF statement. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.