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.

Suppose you have a list of members of staff, split into teams. The following example shows you how to create a drop-down list containing the staff members for a selected team. This list varies depending on which team the user is interested in.

For this example, we begin with a list of members of each team, as shown below:

Data for Variable Drop Down Menu Example

We will first range of cells that will show the team members for any selected team. The team name is typed into cell G2, and a formula in cells E2-E6 returns the members of the selected team.

The following formula, which combines the Hlookup and the Row functions, produces the names of the team members for any team name entered into cell G2:

=HLOOKUP($G$2, $A$1:$C$6, ROW(), 0)

This is shown in the spreadsheet below.

Formula for Variable Drop Down Menu Example

In the above spreadsheet the formula "=$G$2" is used to reflect the selected team name in cell E1 and the Hlookup formula in cells E2-E6 is used to display the members of the selected team.

We can now create a drop down list, that takes its possible values from cells E2-E6. Therefore, the contents of this drop down list will also depend on the value of cell G2.

To create a drop-down list in cell G4, from the values in cell E2-E6:

  1. Select cell G4
  2. In Excel 2003:   Select the Data Menu from the top of your Excel spreadsheet and from within this menu, select the option Validation...
    or
    In Excel 2007 and later versions of Excel:   Select the Data Tab from the top of your Excel spreadsheet and from within this menu, select the option Data Validation→Data Validation...
  3. The Validation menu will appear. Within this menu :
    1. In the first box, select the option 'List'. This should cause further options to appear.
    2. Ensure the option 'In-cell dropdown' is ticked
    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. This is shown below.

Final Result for Variable Drop Down Menu Example


Final Result for Variable Drop-Down List, Depending on Another Drop-Down List

To make your spreadsheet look that bit more professional, it is a good idea 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.


Click here for a further example of creating a drop-down list using excel data validation
Return to the Excel Formulas Page

Return to the ExcelFunctions.net Home Page