Drop-down list Excel formula

Home » Excel-Formulas » Variable-Drop-Down-List

Use Excel Formulas to Create a Variable Drop-Down List

The following example shows you how to use Excel formulas to create a variable drop-down list.

(If you are not familiar with drop-down lists in Excel, you may want to first view the page on How to Create a Basic Drop-Down List in Excel).

How to Create a Variable Drop-Down List - Example

The spreadsheet below contains a list of members of staff, split into teams. We can use this data to create a drop-down list that contains just the staff members for a selected team, and automatically updates when a different team name is selected.

Step 1 - Create a List of Team Members that Update According to the Contents of a Single Cell

We start by creating a range of cells (cells E1-E6 in this example), that display the team members for any team name that is typed into cell G2. The steps to do this are:

1. Type a team name into cell G2 (e.g. 'Admin' has been entered into the spreadsheet below);

2. Insert the following formula into cell E1:

   =$G$2

3. Insert the following formula into cell E2:

   =HLOOKUP($G$2, $A$1:$C$6, ROW(), 0)
4. Copy the formula in cell E2 into cells E3 - E6.

These formulas are displayed in the spreadsheet below:

Note that, in the above example spreadsheet, the formula "=$G$2" in cell E1 reflects team name that is entered in cell G2, and the formulas in cells E2 - E6 use the Hlookup and Row functions to look up the members of this team.

Step 2 - Create a Drop Down List Containing the Team Members for the Selected Team

In Step 1 above, we created a range of cells (cells E2-E6) that vary according to the team name that is typed into cell G2. We will now use the names in cells E2-E6 to create a drop down list that will automatically update every time the value of cell G2 is changed. We will put this drop-down list into cell G4 of the spreadsheet.

To do this:

1. Select cell G4
2. Click on the Data Validation option within the 'Data Tools' grouping on the Data tab of the Excel ribbon, and from this drop-down menu, select the option Data Validation... (see right).

   This will cause the 'Data Validation' dialog box to open up (see below).

   
   
3. Within the 'Data Validation' dialog box:

   1. In the 'Allow' input box, select the option 'List'. This should cause further options to appear in the dialog box.
   2. Ensure the option 'In-cell dropdown' is checked.
   3. In the 'Source' option box, click on the symbol on the right of the box and use the mouse to select cells E2-E6 (or type in "=$E$2:$E$6").
   4. Click OK.

You will now have a drop-down menu in cell G4, that displays the team members relating to the team name that is currently typed into cell G2 (see below).

Typing a different team name into cell G2 causes the list in cell G4 to automatically update with the new team members.

Step 3 - Optional Extras

To make your spreadsheet look that bit more professional, you might prefer to hide columns A-C, which contain the original data, or place this data onto a different worksheet.

As a further finishing touch, you could also make the cell G2 into a drop-down list, from which a team name can be selected, as shown in the above spreadsheet on the right.