How to Create Drop Down List in Multiple Columns in Excel (3 Ways)

getting result

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.

Step-1:

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.

creating table

Step-2:

Now create another table anywhere in the worksheet where you want to make your list.

creating table

Step-3:

Now we will make a drop-down list using these model names. Select Cell D11, and go to Data and click on Data Validation Icon 1

D11→Data→Data Validation

DROP DOWN LIST MAKING

Step-4:

In the data validation dialogue box, select “List” as the validation criteria. Click on this icon icon 2 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 icon 3 to confirm the selection, and finally click “OK” to make your drop-down list.

Selecting data

A drop-down list is created. Click on this icon icon 4 beside cell D11 to view the list.

getting result

Step-5:

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.

final result

Click “OK” to make the list.

Drop Down list

Step-6:

Now we have to make another two drop-down lists for two other cells. For the “Nikon Lens Model”, the list is,

Drop Down list

And for the “Sony Lens Model”,

Drop Down List

Step-7:

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.

final result

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.

Step-1:

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.

creating table

Step-2:

In cell D13, create a drop-down list using the data from the “Headers” of the lens model columns. Follow this steps

D13→Data→Data Validation

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.

creating table

A drop-down list is created in cell D13. Click on this icon icon 4 to view the list.

drop down list

Step-3:

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,

=OFFSET($D$3,1,MATCH($D13,$D$3:$F$3,0)-1,6,1)

Where,

  • 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

getting result

Click “OK” to get the list from the multiple columns.

result

Step-4:

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.

final result

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.

Step-1:

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.

creating table

Step-2:

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.

drop down list

Click “OK” to make the list. Click on this icon icon 4 beside cell B13 to show the list.

creating list

Step-3:

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

creating table

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

drop down list

Step-4:

Now select cell C13 and go to “Data Validation” and select “List”. In the “Source” box, apply this formula,

=INDIRECT(B13)

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.

making selection

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

drop down list

Step-5:

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.

drop down list

Step-6:

Now select cell D13 and go to “Data Validation” and select “List”. In the “Source” box, apply this formula,

=INDIRECT(C13)

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.

drop down list

Click “OK” to get your job done.

getting result

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.

getting result

 Quick Notes

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

Conclusion

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo