Written by Allen Wyatt (last updated October 26, 2019)
This tip applies to Excel 97, 2000, 2002, and 2003
Alan can use data validation to create a drop-down list of valid choices for a cell. However, what he actually needs is more complex. He has a large number of item names with associated item codes. In cell B2 he can create a data validation list that shows all the item names (agitator, motor, pump, tank, etc.). The user can then choose one of these. When he references cell B2 elsewhere, however, he wants the item code—not the item name—returned by the reference. Thus, the reference would return A, M, P, TK, etc. instead of agitator, motor, pump, tank, etc.
There is no direct way to do this in Excel. The reason is because data validation lists are set up to include only a single-dimensional list of items. This makes it easy for the list to contain your item names. However, you can expand how you use the data validation list a bit to get what you want. Follow these steps:
Figure 1. The Define Name dialog box.
Figure 2. The Data Validation dialog box.
With these steps done, people can still use the data validation drop-down list to select valid item names. What you now need to do is reference the item code from the data table you set up in step 1. You can do that with a formula such as this:
=VLOOKUP(B2,OFFSET(Itemlist,0,0,,2),2,FALSE)
This formula can be used on its own (to put the desired item code into a cell) or it could be used within a larger formula, anyplace you would have originally referenced B2.
If, for some reason, you cannot create a data table for your item names and codes, you could approach the problem by creating an array formula:
=INDEX({"A","M","P","TK"},MATCH(B2,{"agitator","motor","pump","tank"},0))
As with all array formulas, you enter this one by pressing Ctrl+Shift+Enter. The biggest drawback to it is that it can quickly become unwieldy to keep the formula updated and there is a "viability limit" on how many pairs of codes and items you can include in the formula. (The limit is defined by formula length, so it depends on the length of your item names.) Also, this approach is good to only return the item code in another cell, rather than including it as part of a larger formula.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12077) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Returning Item Codes Instead of Item Names.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Sometimes you need to look backward, through the information above your formula, to find the data you need. This can be ...
Discover MoreWant to return more than a value when doing a lookup? Here's one way to do it by adding an IF clause to your formula.
Discover MoreThe VLOOKUP function, like other lookup functions in Excel, is not case sensitive. In other words, it doesn't matter ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments