How to Make a Drop-Down List Based on Formula in Excel (4 Ways)

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. Today in this article we will learn some of the functions based on drop-down lists.

Download Practice Workbook

Download this practice sheet to practice while you are reading this article.

Making a Formula Based Drop-Down List (4 Ways)

1. Drop-Down List Based on the OFFSET Function

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.

i. Creating a Standard Drop-Down List

Step-1:

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

Step-2:

Select a cell where you want to make the list (D3) and go to “Data”, click on “Data Validation”

Data → Data Tools → Data Validation

In the data validation dialogue box, select “List” as the validation criteria. Click on this icon  to continue.

Step-3:

In the source field, apply the “OFFSET” formula. Insert the values and the final formula is

Where,

• Reference is $B$4
• Rows and Columns is 0
• [height] is 15

Click “OK” to get the formula-based drop-down list.

ii. Creating a Dynamic Drop-Down List

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

Step-1:

We will use the same datasheet here. Now in the source field, apply this “OFFSET” formula.

Insert the values and the final formula is

Where,

• Reference is $B$4
• Rows and Columns is 0
• [height] is COUNTIF($B$4:$B$100,”<>”)). The COUNTIF function counts the non-blank cells in the range B4:B100.

Click “OK” to get the formula-based dynamic drop-down list.

Step-2:

So we have got our 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 Create Dynamic Dependent Drop Down List in Excel

2. Drop-Down List Based on the IF Function

We will now make a drop-down list based on the “IF” function. Follow these steps to understand.

Step-1:

Consider a situation where we have some book of “Charles Dickens” and corresponding price name in the “Price” column. We will make the “IF” formula-based drop-down list to find the price.

Step-2:

Make those steps to create a drop-down list of the books. Select the table as your source data.

Click “OK” to get the list.

Step-3:

Now on cell “F4” beside the list, apply the “IF” formula. After inserting the values the final formula is,

Where,

• Logical_test is E4(Data in the drop-down list)
• [Value_If_True] is the corresponding price of the book
• [Value_If_False] then the function will move on to the next book and search the match.

Press “OK” to apply the formula.

Step-4:

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

Read More: How to Make a Drop Down List in Excel (Independent and Dependent)

Similar Readings

• How to Extract Data Based on a Drop Down List Selection in Excel
• How to Add Blank Option to Drop Down List in Excel (2 Methods)
• Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)
• How to Add Item to Drop-Down List in Excel (5 Methods)
• Hide or Unhide Columns Based on Drop Down List Selection in Excel

3. Drop-Down List Based on the VLOOKUP Function

We can use the “VLOOKUP” function to make a formula-based drop-down list.

Step-1:

In this example, we have some “Book Name” and their “Price”. We will make a formula-based list using the “VLOOKUP” formula.

Step-2:

Make a drop-down list in the cell “E4” using the source data as the “Book Name”.

Click “OK” to proceed.

Step-3:

Now in the cell “F4”, apply the VLOOKUP function.

The formula is

Where,

• Lookup_Value is E4 (The drop-down list)
• Table_Array is B4:C18
• Col_Index_Num is 2
• We want the exact value (FALSE)

Press “Enter” to apply the formula. The task is done.

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

Read More: VLOOKUP with Drop Down List in Excel

4. Dependent Drop-Down List Based on the INDIRECT Function

The dependent drop-down list is also a formula-based list and dynamic. Let’s discuss.

Step-1:

Consider this example where the “Fruit” and “Vegetables” columns are given with some data. We need to make a dependent list from that.

Step-2:

Now create a drop-down list from the column’s name.

Click “OK” to continue

Step-3:

We have our drop-down list for the columns. To complete the task, select the “Fruit” and “Vegetable” column, go to “Formula” and in the “Name Manager”, click on “Create From Selection”.

Formula → Name Manager → Create from Selection

Step-4:

A new window popped out. Check on the “Top Row” and click “Ok”

Step-5:

Now our final task is here. 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.

Step-6:

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

Read More: How to Create Dependent Drop Down List with Multiple Words in Excel

Quick Notes

⏩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”.

Conclusion

A drop-down based on the formula makes our job a lot more easy and comfortable. We discussed five different methods to do that. If you have any confusion or thoughts regarding this article please let us know in the comment section.

Related Articles

• How to Create a Form with Drop Down List in Excel
• Creating a Drop Down Filter to Extract Data Based on Selection in Excel
• Create a Searchable Drop Down List in Excel (2 Methods)
• Create Excel Drop Down List from Table (5 Examples)
• Excel Drop Down List Not Working (8 Issues and Solutions)
• How to Edit Drop Down List in Excel (3 Basic Approaches)