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.