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.
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.
You are welcome to download the practice workbook from the link below.
3 Ways to Use 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.
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.
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.
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.
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
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
We have found the players from Tennis only. Change the sports category
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.
A New Name dialog box will appear in front of you, set a name for the range there.
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.
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).
Now we will modify the earlier formula, replacing the cell references with the named ranges.
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.
Change of sport will lead to different values with the drop-down list.
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
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.
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
This will bring the players from the respective sporting fraternity.
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.
Here we have set the range names as the sports.
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.
- How to Create Dynamic Dependent Drop Down List in Excel
- Create a Drop Down List From Another Sheet in Excel (2 Methods)
- VLOOKUP with Drop Down List in Excel
- How to Create Drop Down List in Multiple Columns in Excel (3 Ways)
- Auto Update Drop Down List in Excel (3 Ways)
- Multiple Dependent Drop-Down List Excel VBA (3 Ways)