How to make Dependent Dropdown list with XLOOKUP

I have never imagined that it would be so easy to make a dependent dropdown list with XLOOKUP, follow these simple steps & don’t forget to see this video.

XLOOKUP is only available for Office365 (mac & windows)

In the above dataset, when a user selects the countries the relevant cities on the right table must appear in our countries tables as a dropdown list.

You can convert your data to Excel Table, which will be quicker to write the formula or you may use name ranges & In the video, I have explained how to construct the formula with the name range in the video.

Step 1

Select the range to make the first dropdown list of countries then go to Ribbon Tab > Data > Data Validation

Data Validation box will pop-up.

Under the Setting tab, select Allow > List and then select the source icon.

Select the range to create our first dropdown list

Once range is selected click on the down arrow icon 

We are done making the first dropdown list & now let us see how to make the dependent list

Step 2

To make a dependent list is very simple you just simply need to write this formula

Once we enter the formula, XLOOKUP will search the country “India” and return the values which are in India column and with new dynamic arrays, the information has spilled into each cell. However we will not keep this formula in the cell, below picture is just for understanding purpose

=XLOOKUP(A3,$D$2:$F$2,$D$3:$F$6)

You can copy the above formula by selecting the copy button in right conner

Select the range of Data Validation to need to be validated, in our case it’s from “C3:C6”

Go to Ribbon Tab > Data > Data Validation

Under the Data Validation setting tab, select Allow > List and then paste this formula in the source box.

Congratulations ⭐️🎖, now you know how to make the dependent dropdown list using XLOOKUP

Was the information helpful in this blog?

• If yes, hit that share button and show the excitement to the world.
• Subscribe to our free posts.

If no, please let us know what to improve.

Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.