Excel formula based on drop down list
In this post, well explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table.
Objective
Before digging into the mechanics, lets review our objective with an example.
We would like to allow our user to select items from a drop-down list, and then have Excel automatically retrieve multiple values into separate columns for the selected item, as illustrated below.
Once the user selects an ItemNum from the drop-down, Excel retrieves the related attributes (Description and Price) from a related items table.
Details
There are several moving parts to this solution, and well take them one at a time. Overall, the drop-down list is created with the Data Validation feature, and provides the user with the list of choices based on the item table. Once the user selects an item from the drop-down, Excel formulas populate the price and description columns with the VLOOKUP function. The steps that follow demonstrate:
- How to store the items in a table
- How to create a named reference
- How to create the drop-down with the data validation feature
- How to retrieve the item attributes with the VLOOKUP function
Lets do this.
Store the Items in a Table
The first step is to store the items in a table. The main benefit here is that the table will automatically expand to include any new items added in the future. To store the items in a table, use the Insert > Table ribbon icon. (Note: Tables were first available in Excel 2007 for Windows.)
We want to assign our table a descriptive name, so rather than using the default table name of Table1, well change it to tbl_items with the Table Tools > Design > Table Name ribbon field.
The resulting items table, named tbl_items, is shown below.
Create a Named Reference
In order to create our drop-down list based on the ItemNum column of the tbl_items table, well need to set up a custom name. To do this step, simply select the ItemNum column, excluding the header, and then open the Name Manager (Formulas > Name Manager).
In the Name Manager dialog, click the New button. In the resulting New Name dialog, set the Name to dd_items (or any other preferred name) and ensure that the name refers to the table column tbl_items[ItemNum] as shown below.
Click OK and then close the Name Manager dialog.
Create the Drop-Down
Well create the drop-down with the Data Validation feature. Simple select the cell that should contain the drop-down, and then the Data > Data Validation icon. We want to Allow a List equal to dd_items, as shown below.
If youd like your user to add multiple items, as weve done in the first animation above, it is convenient to store the input range in a table as well. That way, the Table will automatically carry the drop-down into any new rows.
Retrieve Attributes with VLOOKUP
Once the user selects an item from the drop-down, wed like Excel to retrieve the related attributes from our items table (tbl_items). There are a wide variety of ways to accomplish this part, and well use a basic VLOOKUP function. If you have a preferred approach, please share by posting a comment below.
In the Description column, well ask the VLOOKUP function to look up the ItemNum in the items table and retrieve the related description with the formula below.
=VLOOKUP([@ItemNum],tbl_items,3,0)Where:
- [@ItemNum] is the lookup value, the item num in our input table. (If you arent using an input table, you can use a traditional cell reference, such as A1.)
- tbl_items is the lookup range, the items table
- 3 is the table column that has the value we wish to return, the 3rd column, the description column
- 0 tells the function to perform an exact match of the item num
Optionally, if we wanted to return an empty cell instead of an error, we could wrap the function in an IFERROR function, as follows.
=IFERROR(VLOOKUP([@ItemNum],tbl_items,3,0),"")Where:
- VLOOKUP() returns the related description, or #N/A if not found
- tells Excel to return an empty text string if the VLOOKUP is an error
We use a similar formula to return the price from the 2nd column, and we are all set. Now, the user can select an item num from the drop-down and Excel automatically retrieves the description and price from the item table, as shown below.
If you prefer other approaches to the steps presented above, please share! Post a comment belowthanks!