Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Read more: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)


Practice Workbook

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


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

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 the IFS function and the INDIRECT function to do the same task. Let’s get familiar with the dataset first.

excel drop down list if statement

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.

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

  • 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.
=IF(F7="Football",$B$7:$B$11,IF(F7="Cricket",$C$7:$C$11,IF(F7="Tennis",$D$7:$D$11,)))

In the formula, we have used nested IF functions which give the output $B$7:$B$11 when F7 cell contains Football as value or $C$7:$C$11 when F7 has value Cricket or $D$7:$D$11 when F7 has value Tennis.
  • 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.

excel drop down list if statement result


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 Name Range. For that, follow the given steps.

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

Generate Drop-Down List Using IF Statement with Name Ranges

  • 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.
=IF(F7="Football",Football_Player,IF(F7="Cricket",Cricket_Player,IF(F7="Tennis",Tennis_Player,)))

Note: in the formula, we used Name Range instead of a cell reference.
  • 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.

excel drop down list if statement result


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.
=IFS(F7="Football",Football_Player,F7="Cricket",Cricket_Player,F7="Tennis",Tennis_Player)

Note: in the formula we used 3 conditions F7=”Football” and F7=”Cricket” and F7=”Tennis”. Also, their corresponding true value Football_Player, Cricket_Player and Tennis_Player which are name ranges.
  • 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.

excel drop down list if statement result


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.

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

  • 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.
=INDIRECT(F7)

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

excel drop down list if statement result


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. Visit our ExcelDemy Website for similar articles regarding Excel.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo