MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dependent Validation Using Arrays


October 18, 2018 - by Bill Jelen

Dependent Validation Using Arrays

Ever since Data Validation drop-down menus were added to Excel in 1997, people have been trying to work out a way to have the second drop-down menu change based on the selection in the first drop-down.

For example, if you choose Fruit in A2, the drop-down in A4 would offer Apple, Banana, Cherry. But if you choose Herbs from A2, the list in A4 would offer Anise, Basil, Cinnamon. There have been many solutions over the years. I've covered it at least twice in the MrExcel Podcast:

With the release of the new Dynamic Array formulas in Public Preview, the new FILTER function will give us another way to do Dependent Validation.

Say that this is your database of products:

Build validation based on this database
Build validation based on this database

Use a formula of =SORT(UNIQUE(B4:B23)) in D4 to get a unique list of the classifications. This is a brand new type of formula. One formula in D4 returns many answers that will spill into many cells. To refer to the Spiller Range, you would use =D4# instead of =D4.


A unique list of the classifications
A unique list of the classifications

Select a cell to hold the Data Validation menu. Choose Alt+D L to open Data Validation. Change Allow to "List". Specify =D4# as the source of the list. Note that the Hashtag(#) is the Spiller - it means that you are referring to the whole Spiller Range.

Set up Validation pointing to the list in =D4#.
Set up Validation pointing to the list in =D4#.

The plan is that someone will choose a classification from the first drop-down menu. Then, a formula of =FILTER(A4:A23,B4:B23=H3,"Choose Class First") in E4 will return all of the products in that category. Note that use of "Choose Class First" as the optional third argument. This will prevent a #VALUE! error from appearing.

Use a FILTER function to get the list of products that match the selected category.
Use a FILTER function to get the list of products that match the selected category.

There could be a different number of items in the list depending on the category selected. Setting up Data Validation pointing to =E4# will expand or contract with the length of the list.

Watch Video

Download Excel File

To download the excel file: dependent-validation-using-arrays.xlsx

To learn more about Dynamic Arrays, check out Excel Dynamic Arrays Straight To The Point.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Never delete an Excel file without backing it up first."

Mike Alexander

Title Photo: FĂ©lix Prado on Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.