Home > Excel
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
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...
...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:
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
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.