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

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

In our example, we will select a sports category as Favorite Sports, and depending on the sport, the player names will be shown as Favorite Player.

Select Cell F7.

Go to the Data tab and select Data Validation.

Select List in Allow section and in Source section select the cell range $C$4:$E$4.
Press OK.

The Favourite Sports list is created.

Select Cell F9 where we will create the drop-down list.
Open the Data Validation window and enter 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.

The drop-down list is created.

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

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

Create the drop-down list for Favorite Sports.

Select the cell range for Football Player.
Go to the Formulas tab and select Define Name.

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

Define the name range Cricket_Player and Tennis_Player.
Select Cell F9 as we want to create the drop-down list there.
Open the Data validation window.
In the Data Validation window, select List in Allow section and enter 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 Named Range instead of a cell reference.

The drop-down list is created. You can select a different sport from the Favorite Sports list and the Favorite Player list will update.

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

Create the Favorite Sports drop-down list.

Select Cell F9 and open the Data Validation window.
Select List in Allow section and enter the following formula in the Source section and 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”. We also used their corresponding true value Football_Player, Cricket_Player and Tennis_Player which are name ranges.

The drop-down list is created. Select different sports from the Favorite Sports drop-down list and the Favorite Player drop-down list will update accordingly.

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.

We will create a conditional drop-down list using the INDIRECT function.

Create the Favorite Sports drop-down list.

Create the Name Range for players. Give the names as the list element in the Favorite Sports drop-down list i.e Football, Cricket and Tennis.

Select Cell F9 to create a conditional drop-down list.
Open the Data Validation window.
Select List in Allow section and enter the following formula in Source section and press OK.