Home > Excel Solutions > 

An Excel 2003 & 2007 Tutorial

Returning Items from a List
Using Excel's Data Validation

You easily can return any number of items from an Excel
database. Just use Excel's Data Validation feature to return the
item you choose, and INDEX-MATCH to return the other items.

by Charley Kyd
January, 2008

Dawn has a list of people and their email addresses in her spreadsheet. She wants to select a name from a list and then display the person's email address.

I assume that she has a spreadsheet that looks something like this figure.

Here, cell B11 uses data validation to return the selected name from the list of Names. Then a formula in cell B12 returns the email address from the list of Addresses.

You could use a similar setup for any data you want to retrieve from a list.

To begin, enter and format the data as shown in rows 2 through 9. One advantage to using the two shaded rows in this figure is that can set things up so that we can safely enter new rows of data anywhere between those rows. The key is that we assign range names that begin in the top shaded row and end in the bottom shaded row.

To assign the two names, select the range A2:B9. Then press Ctrl+Shift+F3 to launch the Create Names dialog; ensure that only Top row is checked; then choose OK.

Alternatively, to launch the Create Names dialog in Classic Excel, choose Insert, Name, Create. In New Excel, choose Formulas, Defined Names, Create From Selection.

Excel's Data Validation tool offers a quick way to choose an item from a list. It validates your results because if you use its dropdown list box, the cell will contain only items from the list.

To set up the data validation list, first select cell B11 as shown in the figure above and then launch the Data Validation dialog. (In Classic Excel, choose Data, Validation. In New Excel, choose Data, Data Tools, Data Validation, Data Validation.)

In the Settings tab, choose List from the Allow dropdown list box. After you do so, the dialog displays a Source textbox. Enter...

=names

...in the textbox. This tells the data validation tool to return data from the Names range name. Then choose OK.

With data validation set, you'll see the dropdown arrow when you select cell B11. And when you click on the arrow you'll see the list, as shown here.

The next step is to enter a formula that returns the email address for the name returned to cell B11.

To set up the address, enter this formula in cell B12:

=INDEX(Addresses,MATCH(B11,Names,0))

The MATCH function in this formula looks up "Sue" in the Names list and returns the index number for that name. Then the INDEX function returns the corresponding email address from the Addresses list.

Notice that the last argument for the MATCH function is zero. This setting tells the function that the list isn't necessarily sorted, to return the index for the first matching item it finds, and to return #N/A if it can't find a match. Using other arguments for this setting causes the MATCH function to behave quite differently.

If there were other columns of data in this list, like Titles or Genders, other INDEX-MATCH formulas could return that information as well. You would just use the range names Titles or Genders in the formula above, rather than Addresses.