Drop Down List with If Statement – Excel

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

drop down list if statement Main

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 in an original drop-down list can be done with an IF statement in the data validation function of Excel.

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

drop down list if statement 01

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

drop down list if statement 02

 

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

  1. Click OK.
  2. Click on the drop-down list to see the list of available fruit.

drop down list if statement 03

  1. 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”.

drop down list if statement 04

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

We can expand on the formula above by using nested If statements.

drop down list if statement 05

  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.

drop down list if statement 06

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

drop down list if statement 07

 

Once again we are using Range Names in the formula.

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

drop down list if statement 08

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

drop down list if statement 09