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 as shown below.Ĭlick OK and then close the Name Manager dialog. In the Name Manager dialog, click the New button. To do this step, simply select the ItemNum column, excluding the header, and then open the Name Manager (Formulas > Name Manager). In order to create our drop-down list based on the ItemNum column of the tbl_items table, we’ll need to set up a custom name. The resulting items table, named tbl_items, is shown below. We want to assign our table a descriptive name, so rather than using the default table name of Table1, we’ll change it to tbl_items with the Table Tools > Design > Table Name ribbon field. (Note: Tables were first available in Excel 2007 for Windows.) To store the items in a table, use the Insert > Table ribbon icon.

The main benefit here is that the table will automatically expand to include any new items added in the future. The first step is to store the items in a table.

How to retrieve the item attributes with the VLOOKUP function.

How to create the drop-down with the data validation feature.

Once the user selects an item from the drop-down, Excel formulas populate the price and description columns with the VLOOKUP function. 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. There are several moving parts to this solution, and we’ll take them one at a time. Once the user selects an ItemNum from the drop-down, Excel retrieves the related attributes (Description and Price) from a related items table. 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.

Objectiveīefore digging into the mechanics, let’s review our objective with an example.

In this post, we’ll 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.

Excel if then formula with vlookup