05 Jun 2018

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

##### Horizontal Layout

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

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

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.

1. Simon says:

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!

1. Kevin Lehrbass says:

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

2. Meganathan says:

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)

1. Kevin Lehrbass says:

Thanks for your formula Meganathan! I’ll check it out!
Regards,
Kevin