When you are working with a large database and you need to pick a specific item from a list, a drop-down list can help you in this situation. Using a drop-down list you can select any specific data in seconds. You can make the drop-down list using multiple columns too. Today in this article, we will discuss some methods of creating Excel drop-down list from multiple columns.
How to Create Drop Down List in Multiple Columns: 3 Unique Ways
This section will cover 3 unique ways for Excel drop-down list with multiple columns. Let’s discuss them with proper illustration.
1. Independent Drop-Down List in Multiple Columns
You can create an independent Excel drop-down list with multiple columns.
In the following example, we are given some Camera “Lens Model” and their prospective model names such as “Canon Lens Model”, “Nikon Lens Model”, and “Sony Lens Model”. We have to make drop-down lists using these columns.
- First of all, create another table anywhere in the worksheet where you want to make your list.
- Now we will make a drop-down list using these model names.
- So, select the cell where you want to create a dropdown list (i.e. Cell D11) ->go to the Data tab ->click on Data Validation.
- Next, in the Data Validation dialogue box, select “List” as the validation criteria. And in the Source field window appears. Select the data range from the “Lens Model” column ($B$5:$B$7).
- Click OK to confirm.
- Hence, your desired drop-down list will be created. Click on this icon beside cell D11 to view the list.
- Now we will create another list beside the cell named “Canon Lens Model” (D12). Repeat those previous procedures and select the data array ($D$5:$D$9) as your source field.
- Click OK to make a list.
- Now we have to make two drop-down lists for two other cells. For the “Nikon Lens Model”, the list is,
- And for the “Sony Lens Model”.
- Now that we have all the drop-down lists, we can independently choose options from those lists. For example, for the Nikon Lens Model, we can choose the perspective Lens.
Read More: Create a Searchable Drop Down List in Excel
2. Using OFFSET Function in Multiple Columns
We can use the OFFSET function to make our drop-down list from multiple columns more dynamic.
In this example, we will use the previous dataset. Now create anywhere in the worksheet containing columns “Select Lens”, and “Model”.
In these columns, we will make our lists.
- First of all, in cell D13, create a drop-down list using the data from the “Headers” of the lens model columns. Follow this step like Method 1.
D13→Data tab→Data Validation
- Then, in the Data Validation dialogue box, select List as the Validation Criteria. Now, Select $D$4:$F$4 as your Source data. Remember to check on the “Ignore Blank” and “In-cell Dropdown”.
- Click OK to continue.
- Hence, a drop-down list is created in cell D13. Click on this icon to view the list.
- Now that our primary job is done, we will make a final drop-down list using multiple columns. To do this, select cell E14, and repeat the process of making the drop-down list as shown in the previous methods. Now here in the source box, apply the OFFSET with MATCH functions to use multiple columns simultaneously. The formula is,
- Reference is $D$4
- The row is 1. We want to move 1 row down each time.
- Column is MATCH($D14,$D$4:$F$4,0)-1. Here we used the MATCH formula to make the column selection dynamic. In the MATCH formula, the Lookup value is $D14, lookup_array is $D$4:$F$4, and [match_type] is EXACT.
- [height] of each column is 5
- [width] of each column is 1
- Click “OK” to get the list from the multiple columns.
- So our drop-down list from multiple columns is ready. This list is dynamic. For example, if we choose the “Sony Lens Model”, the list in the “Model” column will show you the Sony lens names.
3. Dependent Drop-Down List in Multiple Columns
The dependent drop-down list is also a formula-based and multiple columns-based list.
In the following example, we are given some continent names under the column “Continent”, other columns showing some country names under those continent names, and the rest of the columns show some city names under those perspective countries.
We need to make drop-down lists using these multiple columns. Now create another table anywhere in the worksheet where you want to get the result.
- First, in cell D13 make a drop-down list using the name of the continents. To make the list, follow the previously discussed procedures. Select the source data $D$4:$F$4.
- Next, click OK to make a list. Click on this icon beside cell D13 to show the list.
- In the next step, we will create “Name Ranges” for those country columns. Select the columns named “Asia”, “Africa”, and “Europe” and 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 select cell E13 and go to Data Validation and select List. In the Source box, apply this formula:
This means that when you select Asia in the drop-down list (D13), this refers to the named range “Asia” (through the INDIRECT function and thus lists all the items in that category.
- Then, click OK. The formula-based dependent list is made.
- Our task isn’t done yet! Our next step is to make another dependent list depending on the value in cell E13! To do this, again go to Formulas and in the Name Manager, click on Create From Selection. Check on the Top Row and click OK when the new window appears.
- Now select cell F13 and go to Data Validation and select List. In the Source field, apply this formula:
This means that when you select “India” in the drop-down list (C13), this refers to the named range “India” (through the INDIRECT function) and thus lists all the items in that category.
- Next, click OK to get your job done.
- So our drop-down lists from multiple columns are done. Now if we choose “Europe” and the country “Germany” the list will show us the corresponding results.
👉 The MATCH function counts the columns as 1,2,3 whereas the OFFSET function counts them as 0,1,2. That’s why you have to add “-1” after the match function MATCH($D13,$D$3:$F$3,0)-1.
👉 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”.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
A drop down list based on multiple columns in Excel makes our job a lot easier and more comfortable. We discussed three different methods to do that. If you have any confusion or thoughts regarding this article please let us know in the comment section.
- How to Add Blank Option to Drop Down List in Excel
- How to Select from Drop Down and Pull Data from Different Sheet in Excel
- How to Create a Form with Drop Down List in Excel
- How to Remove Used Items from Drop Down List in Excel
- How to Remove Duplicates from Drop Down List in Excel
- How to Fill Drop-Down List Cell in Excel with Color but with No Text
- [Fixed!] Drop Down List Ignore Blank Not Working in Excel
- How to Make Multiple Selection from Drop Down List in Excel
- How to Autocomplete Data Validation Drop Down List in Excel
- Hide or Unhide Columns Based on Drop Down List Selection in Excel