How to use IF Statement to Create a Drop-Down List in Excel

It’s quite often that 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.

Before diving into the session, let’s get to know about the dataset which is the base of our examples.

Dataset - 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 IF.

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 complex data.

Practice Workbook

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

Using IF Statement to Create Drop-Down List

Here our example will be such that, we will select a sports category, and depending on the sport the player names will be shown.

Drop down list questions - Excel Drop Down List IF Statement

First of all, create a drop-down list for the sports category. We will explore the Data Validation from the Data tab to create the list. For clarity feel free to visit the article regarding making a drop-down list.

Initial list - Excel Drop Down List IF Statement

Here in the Source field for the list, we have inserted the range of cells that contain the sports category. Now you will find the drop-down list of sports.

Drop down list - Excel Drop Down List IF Statement

1. IF Statement with Direct Cell Reference

We will create a drop-down list where it will show the name of players based on the selected game. Our agenda is telling that we will use the IF function, you can check the function in this IF article.

Category set - Excel Drop Down List IF Statement

For example, here we have selected Tennis as the sport. So we need to see only the tennis players on the drop-down list, not all the names.

To create as such, open the Data Validation dialog box for this cell (select the cell where you want the drop-down list), and at the Source field there insert the following formula

=IF(G5="Football",$B$6:$B$12,IF(G5="Cricket",$C$6:$C$12,IF(G5="Tennis",$D$6:$D$10,)))

IF Formula - direct cell reference - Excel Drop Down List IF Statement

Here we have used three IF functions because we have three sports categories to match with. Within each IF we have checked whether the sport is Football, Cricket or Tennis. And for the if_true value, we have selected the cell range for the respective game.

Now click OK. And you will see the drop-down icon. Click that    

Conditional Drop - down list - Excel Drop Down List IF Statement

We have found the players from Tennis only. Change the sports category

Category change - list updated - Excel Drop Down List IF Statement

You will find the drop-down list showing players from that sport.

2. Name Ranges with IF Statement

Earlier we have seen setting the values using cell reference. In this section, we will complete the same task using named ranges.

For providing a name to the range, select the range you want to use, and select the Define Name from the Defined Names section within the Formulas tab.  

Name Range define - Excel Drop Down List IF Statement

A New Name dialog box will appear in front of you, set a name for the range there.

Set name to a range - Excel Drop Down List IF Statement

Here for the footballers, we have named it “Football”. Feel free to use any name you prefer. Make sure to recheck the range before clicking OK.

Set name to a range 2 - Excel Drop Down List IF Statement

Similarly set names for cricketers and tennis players ranges. Here we have set “Cricket” for the cricketers (image above) and “Tennis” for the tennis players (image below).

Set name to a range 3 - Excel Drop Down List IF Statement

Now we will modify the earlier formula, replacing the cell references with the named ranges.

=IF(G5="Football",Football,IF(G5="Cricket",Cricket,IF(G5="Tennis",Tennis,)))

IF Formula - Name Range - Excel Drop Down List IF Statement

Here we have used the name of the ranges, where earlier we have used the cell references directly.

The mechanism of this formula is the same as earlier and will return the players for the selected sport.

Category change - list updated - Excel Drop Down List IF Statement

Change of sport will lead to different values with the drop-down list.

Category cricket - cricket players - Excel Drop Down List IF Statement

3. Use of IF Family Function

Instead of the IF function, we can use a family function of it: IFS. Since we need to check multiple logics, this IFS function will be useful then. To know about the function, visit this IFS article.

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

The formula we are going to use is the following one

=IFS(G5="Football",Football,G5="Cricket",Cricket,G5="Tennis",Tennis)

IFS Formula - Excel Drop Down List IF Statement

We have written the formula in the Source field. And now click OK. This will provide the drop-down list having the players from the selected team.

Category cricket - cricket players - Excel Drop Down List IF Statement

A Useful Alternative

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. To know more about the function visit the article: INDIRECT.

We will provide the cell reference that contains the name of the sport. So, the formula will be

=INDIRECT(C5)

INDIRECT formula - Excel Drop Down List IF Statement

This will bring the players from the respective sporting fraternity.

Category football - list footballer - Excel Drop Down List IF Statement

Here we have found the players from Football.

Remember, you need to set the name of the ranges exactly the same as values from the sports selection drop-down list.

Name of Ranges same as category - Excel Drop Down List IF Statement

Here we have set the range names as the sports.

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.

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