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 a drop-down list from multiple columns in Excel.
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
Create Drop Down List in Multiple Columns (3 Ways)
1. Independent Drop-Down List in Multiple Columns
You can create an independent drop-down list using multiple columns. Let’s discuss.
In the following example, we are given some Camera “Lens Model” and their perspective model name such as “Canon Lens Model”, “Nikon Lens Model”, and “Sony Lens Model”. We have to make drop-down lists using these columns.
Now 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. Select Cell D11, and go to Data and click on Data Validation
In the data validation dialogue box, select “List” as the validation criteria. Click on this icon to continue. Now a source field window appears. Select the data range from the “Lens Model” column ($B$4:$B$6), and click on this icon to confirm the selection, and finally click “OK” to make your drop-down list.
A drop-down list is 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$4:$D$8) as your source field.
Click “OK” to make the list.
Now we have to make another 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 Nikon Lens Model, we can choose the perspective Lens.
2. Using the 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.
In cell D13, create a drop-down list using the data from the “Headers” of the lens model columns. Follow this steps
In the data validation dialogue box, select List as the validation criteria. Now Select $D$3:$F$3 as your source data. Remember to check on the “Ignore Blank” and “In-cell Dropdown”. Click “OK” to continue.
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 E13, 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 formula to use multiple columns simultaneously. The formula is,
- Reference is $D$3
- The row is 1. We want to move 1 row down each time.
- Column is MATCH($D13,$D$3:$F$3,0)-1. Here we used the MATCH formula to make the column selection dynamic. In the MATCH formula, the Lookup value is $D13, lookup_array is $D$3:$F$3, and [match_type] is EXACT.
- [height] of each column is
- [width] of each column is
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. Let’s discuss.
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 showing 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.
In Cell B13 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$3:$F$3.
Click “OK” to make the list. Click on this icon beside cell B13 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 C13 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 (B13), this refers to the named range “Asia” (through the INDIRECT function) and thus lists all the items in that category.
And 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 C13! To do this, again go to “Formula” 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 D13 and go to “Data Validation” and select “List”. In the “Source” box, 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.
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.
Read more: Multiple Dependent Drop-Down List Excel VBA
👉 The MATCH function counts the columns as 1,2,3 where the OFFSET function counts 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”.
A drop-down list based on multiple columns makes our job a lot more easy and 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 Create Dynamic Dependent Drop Down List in Excel
- Create a Drop Down List From Another Sheet in Excel (2 Methods)
- How to Make Multiple Selection from Drop Down List in Excel
- Excel Drop Down List Depending on Selection
- Conditional Drop Down List in Excel (Create, Sort and Use)
- VLOOKUP with Drop Down List in Excel