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

getting result

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.

creating table

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

creating table

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

applying formula

Step-3:

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

=OFFSET($B$4,0,0,15)

Where,

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

source field

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

getting result

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

=OFFSET($B$4,0,0,COUNTIF($B$4:$B$100,”<>”))

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.

creating table

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

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.

getting result

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.

final result

Step-2:

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

source fiels

Click “OK” to get the list.

drop down list

Step-3:

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

=IF(E4=”A Tale of Two Cities “,20,IF(E4=”Great Expectations “,25,IF(E4=”Our Mutual Friends “,10,IF(E4=”Oliver Twist “,50,IF(E4=”Little Dorrit”,20,IF(E4=”David Copperfield”,50,0))))))

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.

result

Press “OK” to apply the formula.

final result

Step-4:

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

final result

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.

creating table

Step-2:

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

creating drop-down list

Click “OK” to proceed.

applying formula

Step-3:

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

The formula is

=VLOOKUP(E4,B4:C18,2,FALSE)

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)

getting result

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

final result

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

final result

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.

creating table

Step-2:

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

drop down list

Click “OK” to continue

final list

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

dependent drop down

Step-4:

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

creating name range

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,

=INDIRECT(E4)

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.

new dependent list

And click “OK”. The formula-based dependent list is made.

final result

Step-6:

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

final result

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo