How to Create Drop Down List in Multiple Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset for Excel Dropdown List Multiple Columns

Steps:

  • 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.

Excel Dropdown List Multiple Columns

  • 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.

Excel Dropdown List Multiple Columns

  • Hence, your desired drop-down list will be created. Click on this icon beside cell D11 to view the list.

Excel Dropdown List Multiple Columns

  • 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.

Excel Dropdown List Multiple Columns

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

Excel Dropdown List Multiple Columns

  • And for the “Sony Lens Model”.

Excel Dropdown List Multiple Columns

  • 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.

Excel Dropdown List Multiple Columns

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.

Steps:

  • 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,

=OFFSET($D$4,1,MATCH($D14,$D$4:$F$4,0)-1,5,1)

Where,

  • 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.

Read More: Creating a Drop Down Filter to Extract Data Based on Selection in Excel


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.

Steps:

  • 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:

=INDIRECT(D13)

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:

=INDIRECT(E13)

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.

Excel Dropdown List Multiple Columns


Quick Notes

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


Conclusion

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.


Further Readings


<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

4 Comments
  1. Very practical and well explained. Thank you

  2. Hello. Brilliant ideas . I have a remark : at number 3 Idea the data $D$3:$F$3. is wrong you have to write D4:F4 isn’t it ?

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo