Excel Formula Based on Drop-Down List (6 Suitable Examples)

Using a drop-down list can be a great approach to offer users a selection of options from a predetermined list. It can be applied for both form completion by users and the creation of interactive Excel dashboards. In this article, we will learn how to create and use Excel formula based on a drop down list.

A drop-down list can help you to select any specific data in seconds. The drop-down list is a great helper when you are working with a large database and you need to pick a specific item from a list. You can make your drop-down list manually or by using some specific functions.

How to Create a Drop Down List in Excel (Static and Dynamic)

We can use the OFFSET function to make a drop-down list with the given data. The list can be standard or dynamic. Let’s discuss this.

1. Creating a Static Drop Down List Based on Formula

Consider a situation wherein a worksheet table is given under the column “Book Name”. We need to make a drop-down list from this data using the OFFSET function.

• Select a cell where you want to make a list. We selected cell D5 and go to “Data”, and click on “Data Validation”.

Data → Data Tools → Data Validation

• In the Data Validation dialogue box, select List as the Validation criteria. In the Source field, apply the OFFSET formula. Insert the values and the final formula is

• Click the “OK” button.

• As a result, you will get the formula-based drop-down list.

Read More: How to Populate List Based on Cell Value in Excel

2. Making a Dynamic Drop Down List Based on Formula

The dynamic drop-down list allows you to auto-update your data. Follow these steps to learn.

• We will use the same datasheet here. Now in the source field, apply the following formula. Hence, press the OK option.

Finally, you will be able to create a dynamic drop-down list.

Now let’s check whether this list is linked with a cell value or not. Delete some data from your data list and see that the drop-down list automatically updates itself. So the formula is working correctly.

Read More: How to Change Drop Down List Based on Cell Value in Excel

How to Use Excel Formula Based on Drop Down List: 6 Handy Examples

We will use the IF, VLOOKUP, INDIRECT, SUMIF, CHOOSE, MATCH, and FILTER functions based drop-down list. Let’s say, we have a dataset where some books and the corresponding price of the books are given in columns B and C respectively. Here’s an overview of the dataset for today’s task.

1. Use Conditional IF Function Based on Drop Down List

We will now make a drop-down list based on the IF function. Let’s follow the instructions below to learn!

• Consider a situation where we have some books by “Charles Dickens” and the corresponding price names in the “Price” column. We will make the IF formula-based drop-down list to find the price.
• Create a drop-down list of the books. Select the table as your Source data. Last, click on the OK option.

• As a result, you will get a drop-down list.

• Now select cell F5 and write down the following formula in that cell. Hence, hit Enter.

• So we have got our formula-based drop-down list. Change the source data to check the formula.

2. Apply VLOOKUP Function to Calculate Price of a Book Based on Drop-Down List

We can use the VLOOKUP function to make a formula-based drop-down list. In this example, we have some “Book Name” and their “Price” in columns B and C respectively. We will make a formula-based drop-down list using the VLOOKUP formula.

• Make a drop-down list in the cell “E5” using the source data as the “Book Name”.
• Click “OK” to proceed.

• Hence, you will be able to create a drop-down list.

• Insert the following formula in cell F5, and press Enter.

• Now you can change the source data in the drop-down list to check the formula.

3. Use of Depending Drop Down List Based on INDIRECT Function

The dependent drop-down list is also a formula-based list and dynamic. Consider this example where the Fruit and Vegetables columns are given with some data. We need to make a dependent list from that. Let’s follow the instructions below to learn!

• Now create a drop-down list from the column’s name.
• Click “OK” to continue.

• We have our drop-down list for the columns.

• To complete the task, select the “Fruit” and “Vegetable” column, go to “Formulas” and in the “Name Manager”, click on “Create From Selection”.

Formulas → Defined Names → Create from Selection

• A new window popped out. Check on the “Top Row” and click “Ok”.
• Select cell F4 and go to “Data Validation” and select “List”. In the “Source” box, apply this formula,

• This means that when you select “Fruit” in the drop-down list (E4), this refers to the named range “Fruit” (through the INDIRECT function) and thus lists all the items in that category.
• And click “OK”. The formula-based dependent list is made.

• Now if you change “Fruits” from “Vegetables”, the list will show you the vegetable name.

Read More: How to Create Dynamic Dependent Drop Down List in Excel

4. Apply SUMIF Formula to Sum Values Based on Drop Down List

In this example, we will use the SUMIF function to calculate the total price of a particular product based on the drop-down list. We have some Product IDs, Product Names and Prices in columns B, C, and D respectively. We will make a formula-based drop-down list using the SUMIF formula. Let’s follow the instructions below to learn!

• Create a drop-down list in cell F5 using the source data as the Product.
• Hence, hit the OK option to proceed.

• Now, you will get your desired drop-down list.

• Select cell G5 and write down the following formula in that cell. Hence, hit Enter.

• As a result, you will get $1,452.00, which is the total price of Cherry using the SUMIF function based on the drop-down list.

Notes:

In addition, you can use the SUMPRODUCT function to calculate the sum of values based on the selected option from a drop-down list.

5. Combine CHOOSE and MATCH Functions to Calculate Geometry Parameters Based on Drop Down List

Let’s say, we have the radius of the sphere. We will calculate the surface area or the volume of the sphere using the CHOOSE and MATCH functions from the drop-down list. Follow the steps below to learn!

• Make a drop-down list in cell E5 using the source data as the Parameter.
• After that, press the OK option to proceed.

• Hence, you will be able to create a drop-down list.

• Type the following formula in cell G5 and press Enter.

• Hence, you will get 523.6 cubic units, which is the volume of the sphere.

6. Use FILTER Function Based on Selection of Drop Down List

Consider this example where the Sales Representatives, State, Product, Quantity and Sales columns are given with some data. Using this dataset, we will calculate the sales statements of a particular state based on the drop-down list. Let’s follow the instructions below to learn!

• Press Ctrl + T to create a table. Hence, select data range $B$4:$F$24 and hit the OK option.

• As a result, you will be able to create a table. Now give the name of the table as Sales.

• After that, we will sort the data of the state column alphabetically. To do that, open a new sheet and type the below formula in cell B5.

• Now, create a drop-down list in cell J5 using the source data as the State.
• Hence, hit the OK option to continue.

• After that, you will be able to create a drop-down list with the helper sheet named List.

• Further, write down the following formula in cell H7 to find out the sales statements of Alaska from the drop-down list and hit Enter.

Read More: Create Excel Filter Using Drop-Down List Based on Cell Value

Things to Remember

• While creating a dynamic drop-down list, Make sure that the cell references are absolute (such as $B$4) and not relative (such as B2, or B$2, or $B2)
• To avoid errors, remember to check “Ignore Blank” and “In-cell Dropdown”.
• Make sure to properly set up the drop-down list using the Data Validation feature in Excel. This will ensure that the list contains the correct options and that users can only select valid choices.
• When using a drop-down list in a formula, be sure to use the INDIRECT function to reference the selected option. This will allow the formula to dynamically update based on the user’s choice.
• Remember that the options in a drop-down list can be changed by editing the list source in the Data Validation dialog box. Make sure to update the list source if you add or remove options.
• You can use drop-down lists to populate cells with selected options or to filter data based on selected options. Be sure to use the appropriate method depending on your needs.

Frequently Asked Questions

1. Can I use a formula in a drop down list in Excel?

Answer: No, you cannot use a formula directly in a drop-down list in Excel. However, you can use a formula in a separate cell to calculate the options for the drop-down list and then refer to that cell as the source for the list.

2. How do I change the options in a drop down list in Excel?

Answer: To change the options in a drop-down list in Excel, you need to first select the cell or cells where the drop-down list appears. Then, go to the Data tab in the ribbon and click on Data Validation. In the Data Validation dialog box, select List as the validation criteria and edit the options in the Source field.

3. Can I use a drop-down list to filter data in Excel?

Answer: Yes, you can use a drop-down list to filter data in Excel. To do this, you need to first create the drop-down list and then select the cell or cells that contain the data you want to filter. Then, go to the Data tab in the ribbon and click on Filter. In the filter drop-down menu, select Filter by Selected Cell’s Value and choose the cell that contains the drop-down list.

Download Practice Workbook

Downloads this practice workbook for practice while you are reading this article.

Conclusion

A drop-down based on the formula makes our job a lot easier and more comfortable. We discussed how to create drop down list with formula. Also the uses of Excel formula based on drop down list. If you have any confusion or thoughts regarding this article please let us know in the comment section.

Related Articles

• Conditional Drop Down List in Excel
• Excel Dependent Drop Down List
• How to Make Dependent Drop Down List with Spaces in Excel
• How to Create Dependent Drop Down List with Multiple Words in Excel
• How to Extract Data Based on a Drop Down List Selection in Excel

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel