Drop-Down List with If Statement in Excel

This tutorial will demonstrate you how to create a drop-down list with an IF statement in Excel and Google Sheets.

 

 

Create a Custom Drop-down List with Data Validation in Excel

To create a drop-down list which changes according to what the user selects, you can use an IF statement within the data validation feature.

1. Click in C2
2. In the Ribbon, select Data > Data Tools > Data Validation.

 

 

3. Select List from the Allow drop-down box, and then type the following formula:

=IF(C2="Show Fruit", ShowMeat, ShowFruit)

 

 

The formula uses two range names – ShowFruit and ShowMeat. Depending on the option chosen, the corresponding list will show up in the drop-down list.

4. Click OK.
5. Click on the drop-down list to see the list of available fruit.

 

 

6. Click on “Show Meat,” and then click on the drop-down list again. The list will have changed to show the meat list while the top value will have change to “Show Fruit.”

 

 

Create a Custom Drop-down List with a Nested IF Statement in Excel

Expand on the formula above by using nested If statements.

 

 

1. Click in C2.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select List from the Allow drop-down list, and then select Range F2:I2.

 

 

4. Click OK.
5. Click in C3.
6. In the Ribbon, , select Data > Data Tools > Data Validation.
7. Select List from the Allow drop-down list, and then type the following formula.

=IF($C$2="Fruit", fruit, IF(C2="Vegetables",vegetables, IF(C2="Meat", meat, seafood)))

 

 

Once again, we are using Range Names in the formula.

8. Select the food type in the top drop-down list.

 

 

9. The list in the second drop-down list will change accordingly.