Hướng Dẫn Excel formula based on drop down list 2022

Mẹo về Excel formula based on drop down list Mới Nhất

Bạn đang tìm kiếm từ khóa Excel formula based on drop down list được Update vào lúc : 2022-11-12 09:45:00 . Với phương châm chia sẻ Thủ Thuật về trong nội dung bài viết một cách Chi Tiết 2022. Nếu sau khi đọc Post vẫn ko hiểu thì hoàn toàn có thể lại Comments ở cuối bài để Mình lý giải và hướng dẫn lại nha.

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 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 tableHow to create a named referenceHow to create the drop-down with the data validation featureHow 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 table3 is the table column that has the value we wish to return, the 3rd column, the description column0 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!

Additional Resources

Review Excel formula based on drop down list ?

Bạn vừa đọc nội dung bài viết Với Một số hướng dẫn một cách rõ ràng hơn về Video Excel formula based on drop down list tiên tiến và phát triển nhất

Chia Sẻ Link Down Excel formula based on drop down list miễn phí

Quý khách đang tìm một số trong những ShareLink Tải Excel formula based on drop down list miễn phí.

Thảo Luận vướng mắc về Excel formula based on drop down list

Nếu sau khi đọc nội dung bài viết Excel formula based on drop down list vẫn chưa hiểu thì hoàn toàn có thể lại Comment ở cuối bài để Mình lý giải và hướng dẫn lại nha
#Excel #formula #based #drop #list