Excel Course: Drop-Down List

To insert a drop-down list you must have the "Developer" tab.

If it is not shown, click on "File", "Options", "Customize Ribbon" tab and check "Developer".

The tab should now be displayed.

Drop-down List

Click on "Insert" and choose "Combo Box".

Select your drop-down list and click on "Format Control".

In the "Input range" field, enter the range of cells that contain the values you want in your drop-down list (in this case, cities).

In the "Cell link" field, enter the cell where the result should be displayed (the value chosen from the list).

In the "Drop down lines" field, enter the number of values from the list that you want to be visible at once.

Your drop-down list is now ready.

The drop-down list returns a number in cell E6.

How to Display a Text Value Based on the Number Returned

When you select New York, for example, cell E6 will show the value 5 (which is its position in the drop-down list, therefore its position in the table).

To display the text value that is associated with this number, use the INDEX function.

In the "Array" field, enter the cell range and in the "Row_num", the cell that contains the position number (this should be the same information entered in "Format Control").

The results: