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
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.
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.
In the source field, apply the “OFFSET” formula. Insert the values and the final formula is
- 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.
We will use the same datasheet here. Now in the source field, apply this “OFFSET” formula.
Insert the values and the final formula is
- 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.
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.
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.
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.
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.
Now on cell “F4” beside the list, apply the “IF” formula. After inserting the values the final formula is,
- 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.
So we have got our formula-based drop-down list. Change the source data to check the formula.
3. Drop-Down List Based on the VLOOKUP Function
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”. We will make a formula-based list using the “VLOOKUP” formula.
Make a drop-down list in the cell “E4” using the source data as the “Book Name”.
Click “OK” to proceed.
Now in the cell “F4”, apply the VLOOKUP function.
The formula is
- 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.
Now you can change the source data to check the formula in the drop-down list.
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.
Consider this example where the “Fruit” and “Vegetables” columns are given with some data. We need to make a dependent list from that.
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 “Formula” and in the “Name Manager”, click on “Create From Selection”.
Formula → Name Manager → Create from Selection
A new window popped out. Check on the “Top Row” and click “Ok”
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.
Now if you change “Fruits” from “Vegetables”, the list will show you the vegetable name.
⏩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”.
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.