Attribute integer values to list items

Question: I read your tip on dropdown lists in Excel. How can I attribute a value to the items in the dropdown list? I want to use that value in other cell formulas.–Tim

Dear Tim: The article How to add a drop-down list to an Excel cell is a short tutorial on using Excel’s Data Validation feature to limit users to specific items. This feature isn’t flexible enough to do what you want—not directly. If you want to assign a value to each item, you can do so in the sheet and use a VLOOKUP () function to assign that value.

Below you can see a simple sheet with a four-item list in column A. Each item has a corresponding integer value in column B. Using the Data Validation feature, I added a dropdown control to cell D2 and populated it with the text items in column A. In cell E1, I used the following function to return an integer value from column B, based on the selected item in the dropdown:

=VLOOKUP(D1,A1:B4,2)

You didn’t say how you intend to use this value, but it might not be necessary to go to so much trouble. You can grab the item (text) by simply referring to the cell, as I’ve done in cell F2.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s