Dependent Drop Down List in Excel

A data validation drop down list has ‘Beverage’, ‘Food’, ‘Dessert’. You select ‘Food’. In the 2nd drop down list you want to see only ‘Food’ items (‘Chicken’, ‘Meatloaf’, ‘Seafood’). The 2nd list depends on the 1st. This is a dependent drop down list in Excel. I’ll explain my favorite way to solve this. 

 

Dependent Drop Down List Review

Selecting ‘Beverage’ in Category 1 restricts Category 2 values to those that belong to ‘Beverage’.

 

 

 

Selecting ‘Dessert’ in Category 1 restricts Category 2 values to those that belong to ‘Dessert’.

 

Before showing you the solution let’s review two common ways to arrange the data.

 

 

Data Layout

The data can be arranged in a vertical or horizontal layout.

Vertical Layout

 

 

 

 

 

 

 

Horizontal Layout

 

 

 

 

 

Most solutions combine named ranges with the indirect function. I prefer using a single formula that combines 3 functions.

 

 

Excel File

Download my Excel file and follow along.

 

 

Vertical Layout Solution

 

 

 

 

 

 

 

 

 

 

I will show you individual formulas that I’ll combine together into a single formula (Step 4).

 

Step 1  What is the vertical position of selected CATEGORY 1 value?

‘Food’ is selected in cell J13. Where do we find the 1st occurrence of ‘Food’ in range D3:D11 ?

In cell J17 use this formula:  =MATCH(J13,$D$3:$D$11,0)

The result is 5. ‘Food’ is found in the 5th cell (D7) as we start counting in cell D3.

 

Step 2  How many times does selected CATEGORY 1 value appear?

In cell J18 use this formula:  =COUNTIF($D$3:$D$11,J13)

The result is 3. ‘Food’ is listed 3 times in range D3:D11.

 

Step 3  Use OFFSET function to get all CATEGORY 2 items belonging to ‘Food’.

In cell J19 use this formula: =OFFSET($E$2,$J$17,0,$J$18,1)

The result shows a #VALUE! error as there are multiple values (Chicken, Meatloaf, Seafood) inside the OFFSET function.

What text values are inside? In cell J19 go to the formula bar, highlight entire formula and press F9 key (Fn + F9 on a laptop) to see the values. Then press the ‘Esc’ key.

 

Step 4  Combine everything into one OFFSET formula!

Instead of referencing the cells with the MATCH and COUNTIF we can combine them all into one formula.

=OFFSET($E$2,MATCH(J13,$D$3:$D$11,0),0,COUNTIF($D$3:$D$11,J13),1) 

 

Step 5  Paste the OFFSET formula (Step 4) inside of data validation

On the ribbon select ‘Data’ and ‘Data Validation’. In the ‘Settings’ tab under ‘Allow’ select ‘List’.

In ‘Source’ paste the OFFSET formula and press ‘OK’.

 

 

 

 

 

 

 

 

 

 

 

Horizontal Layout Solution

 

 

 

 

 

 

 

 

 

Step 1  What is the horizontal position of selected CATEGORY 1 value?

‘Food’ is selected in cell J13. In cell J17 use this =MATCH(J13,D2:F2,0) The match function returns a 2. 

 

Step 2  How many items does selected CATEGORY 1 value have?

In cell D1 use formula  =COUNTA(D$3:D$50) Drag it to the right (as far as you have  categories).

In cell J18 use formula =INDEX($D$1:$F$1,J17) that displays 3 as ‘Food’ has 3 items.

 

Step 3  Combine COUNTA and INDEX inside of an OFFSET.

Now we combine everything into a single formula: =OFFSET($C$3,0,MATCH(J13,$D$2:$F$2,0),INDEX($D$1:$F$1,MATCH(J13,$D$2:$F$2,0)))

Enter the OFFSET formula in any cell. Test it using the F9 key (press ‘Esc’ once you see the values).

 

Step 4  Paste the OFFSET formula (Step 3) inside of data validation

On the ribbon select ‘Data’ and ‘Data Validation’. In the ‘Settings’ tab under ‘Allow’ select ‘List’.

In ‘Source’ paste the OFFSET formula (Step 3) and press ‘OK’.

 

 

Solution Recap

If your data is formatted as a Table then the formulas will be slightly different. See my Excel file above that contains examples.

The two solutions above only work when the layout is exactly as mentioned. If your Category 1 values are not sorted and/or your Category 2 values have repeats then it would require a different solution.

 

 

About Me

My name is Kevin Lehrbass. I’ve been a Data Analyst since 2001. Working with databases and spreadsheets.

Me with Cali and Fenton 🙂

Check out my recommended Excel training and my YouTube channel.

 

 

5 Comments Dependent Drop Down List in Excel

  1. Simon

    Just want to say thanks for the best description of this problem on the internet! So many others just say “do this, do that, and voila! you’re done” without actually explaining how the process works… which means it’s very difficult to apply the solution on one’s own unique problem. Your page clearly explains how to do this. Brilliant work! (And it also drove me to look up structured references for tables, which has changed my life!

    Reply
    1. Kevin Lehrbass

      Hi Simon,
      It is fun to share ideas! Thanks for reading my post and keep Exceling!
      Cheers,
      Kevin

      Reply
  2. Meganathan

    Hi sir,
    Here is my solution with index formula to avoid Volatile Offset function..,,
    =INDEX($E$3:$E$11,MATCH(J3,$D$3:$D$11,0)):INDEX($E$3:$E$11,MATCH(J3,$D$3:$D$11,0)+COUNTIF($D$3:$D$11,J3)+N(“J3 is Category 1 selected from drop down”)-1)

    Reply
  3. Kadr Leyn

    Thanks for tutorial,
    The template I created by adding dependent drop-down lists on the userform may be useful for users.
    There are 3 dependent drop-down lists and a textbox on the userform. In the first drop-down list, suppliers are listed, categories according to the selected supplier are listed, products according to the selected category are listed. Finally, the price of the selected product is shown in the textbox.
    For source codes and sample workbook : https://eksi30.com/create-dynamic-dependent-drop-down-lists-on-userform/

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *