THE Easiest Multi Level Drop Down List ( easy Double XLOOKUP technique )

An awesome simple explanation of HOW TO BUILD a robust multi row dependent drop down list in Excel

I realised after recording that Mark suggested this exact solution at the start of our UNPIVOT podcast and I didn't realise until listening back to the podcast a few days later.

⏬ Download the file:

Mark’s video

My previous video of “easiest dependent drop down list”

00:00 the aim
00:48 table set up
02:13 range name set up
04:00 the magic XLOOKUP : XLOOKUP
09:00 conditional formatting to flag invalid choices
11:12 if you can’t rely on a sorted table

Did you know I've written a book "Power BI for the Excel Analyst"?

Connect with me

[garrettphillips]

I'm never one to comment on Youtube videos, let alone work related ones....but wow. this video is so informative and better than any other dependent dropdown process I've ever seen. Major props, this is awesome!

[IchBinGigio]

This is the solution that I was looking for. No more complicated use of helper columns or other unnecessary stuff.
Thanks a lot!🙌

[pradeepviv]

Amazing thanks, love the pace and the way you teach. well done

[IvanCortinas_ES]

Excellent tutorial Wyn! Spectacular field work to provide solutions to this problem. Thank you for watching!!!

[Kiloyt]

Thank you very much for these videos. I tried to do something similar in excel about 10-12 years ago and couldn't figure out how to get this done. I was at the point where i was going to try to learn VB to code it in and someone suggested an access database, that was a whole other struggle. Glad to see this finally made it into excel.
I made my data table and used the sort button and did a multilevel sort and then formatted as a table.
I took the dropdowns and nested them into another table I was already working and and they work just fine.
Thanks again.

[iankr]

Many thanks, Wyn. As you and others have said, one day this will be even easier. But seriously, we should be thankful for the vast improvements that MS has introduced into Excel over the past few years - not least of which is the various spilled array functions, which have made my work projects so much easier.

[MelChamb]

This is EXCELlent, immediately Subscribed!! Thank you

[kevintilley]

Thank you so much for a very elegant solution. I was able to assist a colleague with an 8 level, 100, 000 row data set although the first list had to be referenced from a UNIQUE list, even though there were only a dozen or so unique items, as the data validation list range is limited to 2^15 or 32768 items.

[KO]

Hopefully at some point Excel will allow FILTER inside named range formulas (along with table references) then this all becomes much simpler but thanks for thinking outside the box. This is the easiest solution I've seen to date.

[kinjalchauhan]

That was great. It worked :) Thank you.

[Samriddhi]

Hi
Can we make this without converting the cell into the tables

[dispirted]

You and Mark should do a “Duelling Excel” series, in the style of Bill Jelen and Mike Girvin!

[stanTrX]

Thanks. Does it also work inside a table?

[Hortster]

These dependent dropdown list videos couldn't have come at a better time as I need to add them into a project I'm working on. I appreciate the friendly competition to find the easiest method!

[shaibeezz]

Hi, Thank for sharing the easy way for dependent dropdown, one quick query why we cannot use "FILTER" function instead of the "XLOOKUP"

[GeertDelmulle]

Just for the robustness of it all, the technique at the end has my preference.
Thanks for figuring this out.
Then again: if only MS would allow for array calculations whenever ranges are allowed — that would be the real improvement.
If people like you (and me) have to jump through hoops to get the job done, then there’s an other problem.
Thanks for the video!

[qextjpr]

Awesome method. Thank you for sharing. Unfortunately, in office 2021 the values are not unique and the second method breaks due to some functions not being available. It works perfect on Teams/Web and 365 versions. I use it for 6-level dropdown. I made sure to "very hide" the data so nobody messes up sorting and locked some rows to prevent further issues. One thing I would suggest is that formatting could be improved to prevent formatting break if non-consecutive rows are used. For that purpose I used following:
=AND(Q2<>"", ISNA(XMATCH(Q2, <your list name>)))

[billwu]

I am sucessful at generating a level2 list from level 1 selection. However when i tried to copy the formula into name manager, itsays the reference is invalid. Any idea why?

[ExcelProfessionally]

Great video, one small thing I noticed if the List 1 are not in order it does not work to filter correctly, for example:

Colour Green
Colour Red
Movie Terminator
Colour Blue

The returned spilled array include Movie as well (returning all between first match and last match), will be:

Colour Green
Red
Terminator
Blue

[Quidisi]

I was like, "Why not use FILTER(), but I just learned the hard way that Data Validation does not accept dynamic arrays, but only static ranges.
The FILTER() work-around is to utilize it as a helper column, and then name that range, but I like that XLOOKUP() avoids the helper column messiness.
I also just realized that I lied to a coworker. Just yesterday I told him that XLOOKUP() cannot return multiple values, and that he needed to use FILTER(). I'm assuming that when using XLOOKUP() the results must be contiguous - but I'm going to play around with this.

Thanks so much.