Creating dependent Drop List without Any Preparation Table

What is a dependent drop List?

It’s a drop List where the items change based upon your selection from another drop List.

In our example we have a list of Countries in the range A2:I17 and for each country we have a list of Cities. We need to create a drop list for selecting the countries and another list for selecting the cities. If we select a different country from the first list, then the contents of the second list change to reflect our selection from the first list.

You can Download the Exercise file and follow along by clicking on the button here below

This is the source list in the range A1:I17

There are different ways of doing this but all of them have a first step in common, which is creating the first drop list, so, let’s create our first drop list.

Creating the First Drop List:

Select the Cell(s) where you want to create the drop list.

Click on the Data Tab ► Click on Data Validation

Tip: Alternatively use the Shortcut ALT+D+L

In the Data Validation dialog box► Click on the Drop list under “Allow” and select ► ”List”

In the Source Box Select the Top row having the countries Names

=$A$1:$I$1

Select an option from the new list, let’s say France.

Our Goal is to create in cell L2 a second drop List for the Cities and the contents of this list change according to our selection from the first list in cell K2

The classic technique: Naming Ranges + INDIRECT Function

Select the range A1:I17

Go to the Formulas Tab of the Ribbon

Click on “Create Names from Selection”

Tip: You could use the shortcut CTRL + SHIFT + F3

Make sure that Top Row is checked (only this box) ►hit OK

You have now created a named range for each country. You can test it by going to the Name Box and from the Drop List select any country, automatically the related cities are selected.

Now let’s select cell L2 and create our second dependent drop List as before. In the source dialog box we need to refer to our selection from K2.

Excel will not understand that what we mean is the Named Range and it will only return the name of the selected country.

In order to tell Excel that what we mean is a Named Range, we need to wrap the cell reference in an INDIRECT function.

= INDIRECT(K2)

Now we created in cell L2 a dependent drop list. Test it multiple times after selecting a different option from the Countries Drop list in Cell K2. By selecting a different country, you get a different list of cities.

Classic Method 2: Using an Offset Function

Assuming that we have the same number of cities for each country, and all cities start at Row # 2 to Row # 17 (16 rows)

Then we create an Offset function inside the data Validation dialog box as follows:

=OFFSET(reference,rows,cols,height,width)

=OFFSET($A$2,0,MATCH($K$2,$A$1:$I$1,0)-1,16,1)

The Offset function has 5 arguments:

The first 3 arguments specify the starting point, which will always start at cell A2 , then will move zero rows up and down and finally will move for a certain number of columns relative to the selected country (I used a match function for this argument).

The next 2 arguments are for height and width and I set the height to 16 and the width to one column). In a more advanced scenario we can make the height dynamic as well.

Now let’s select cell L2 and create our second dependent drop List as before. In the source dialog box we’ll type our OFFSET Function ► OK

Test by changing countries in K2

Using XLOOKUP Function – No Preparation Table

The Best and easiest method, if you have Microsoft 365 (previously called Office 365) then you can use an XLOOKUP Function

Select cell L2 and open the Data Validation dialog box. In the Source box type the XLOOKUP function. This is a relatively new function (released on 28 August 2019) and it replaces the VLOOKUP, HLOOKUP, LOOKUP,INDEX and MATCH functions.

Although it has 6 arguments, I’ll be providing only the first mandatory 3 arguments:

Lookup Value ► cell K2 (the countries Drop List)
Lookup Array ► Range A1:I1 (The List of countries)
Return Array ► Range A2:I17 (All the Cities)

=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

=XLOOKUP(K2,B1:I1,B2:I17)

Although we do not need to lock cells for a dynamic array function, however when you select cells with your mouse while you are in the source box ► cells are locked with a dollar sign (does not make a difference although we don’t need it)

=XLOOKUP($K$2,$B$1:$I$1,$B$2:$I$17)

Tests your dependent drop lists by changing your selection for the country then check your selection for the cities.