Awesome Trick to Get Dependent Drop Downs in Excel (works for multiple rows too)

Need dependent drop downs or cascading drop-downs in Excel? Use this simple but elegant approach.
This technique relies on the powerful & new XLOOKUP function.
Learn more:
Tutorial with sample workbook - chandoo.org/wp...
Full Excel Course - chandoo.org/wp...

[@Jim-co7dv]

I've been killing myself for 8 hours and 5 other videos...to the point of tears. I don't know how you pulled it off, but your method worked the first time I tried it. Superlative. Thank you.

[@gautamsachdeva307]

THANK YOU my brother searched every video and each video was very complicated, thanks to you in explaining it in very easy way

[@alvinazocar1889]

Thanks a lot! An explanation in less than 10 minutes, how wonderful 🎉

[@BhavikBorda]

This was the best one after referring around 10-12 videos for dependent drop down list. Thanks Man..!!!

[@simonvaughan5654]

Incredible as always Chandoo, I didn't realise I needed this in my life until your video. Thank you!

[@chandoo_]

"I didn't realise I needed this in my life until your video" should be my channel's tag line. 😂

[@GrainneDuggan_Excel]

Thanks so much - this is the simplest solution I have seen for this problem! Quite a lot easier to implement!

[@adityatechzone7724]

Incredibly Simple , Bro

[@grantm9905]

You have saved me, my precious Excel Guru! THANK YOU!

[@chandoo_]

Happy to help!

[@Mathematica1729]

Thank you ! Could you also provide the worksheet for practice?

[@umairarshad8541]

Than you so much Chandoo. Love from Karachi Pakistan.

[@skybox76]

This is a great video and helped a lot. I need to know how you did the conditional formatting. I am also using three columns and all my data is good and working, but how do you do the conditional formatting for stale data? Thanks,

[@tahsinafroz3789]

Thank you, it was exactly what I was looking for.

[@SharonShafiq]

You are amazing! I've been trying to do this all day!

[@tsbcoutinho]

Thank you so much!!! Could you make a video explaining how to conditional format, in this situation? I didn't understand from the videos you have on conditional formating. Which formula to use in the rule of conditional formating? Because I have to relate the value to a list. I don't know how to do this. And the way yours shows the error, was awesome!

[@chandoo_]

Thanks for the comment. I am in the process of making CF video. Stay tuned.

[@madhanrajsam]

Awesome... It worked... Thank you very much.. I have been going through multiple videos and solutions none ot those worked and especially with =indirect formulas in data validation.. Glad it worked with xlookup formula.. I'm not okay with blanks in few lists but its fine... Thank you

[@faridqanet4709]

Thanks a lot, that is what I needed for my dashboard.

[@ritikagoenka5186]

Amazing this formula worked. Is there any way that this formula can be worked in Google sheets as well?

[@_kc14]

Could u share how u automatically highlight row with stale data?

[@RT-sp5cw]

Hi Chandoo, this is an awesome video - you make it clear and easy to follow. I have a question on the CF mentioned in the end though. I watched the video you recommended at the end but am still unclear how to implement the CF to highlight if the options in the drop-downs are mismatched. Grateful for yr advice!

[@ExoticIndiaadmin]

chandoo bhai your work is awsome thanks

[@arbazahmad7177]

Excellent 🎉.. specially the Pushpa t-shirt.. Thanks for sharing 👍

[@chandoo_]

My pleasure 😊

[@aynino]

This is brilliant, thanks for sharing!

[@chandoo_]

Glad you enjoyed it!

[@suryasabniveesu6946]

Like how we teach our kids A,B,C,D the same way he explains the concepts

[@chrism9037]

Excellent, thanks Chandoo!

[@m.m.basetolimishkat5846]

Hello Chandoo. This is a very helpful video. I am wondering if can we use spin button from the developer tab instead of drop down list. For example: the first spin button will spin category and the second spin button will spin only those products of a category selected by first spin. Thanks

[@alysonjwilson]

Such a cool feature! Thank you.

[@maruthiprasadsivapuram9027]

Nice work, how to link prices of each items after the final selection?

[@lakshmik6610]

Your t shirt is very good. Where you have bought??

[@sudheerreddy4u]

Thank you Chandu😊

[@ravikiran2345]

Content is as good as your t-shirt (taggede le😊)

[@chandoo_]

🧔👋

[@Excelingtheeasyway]

Hey Chandoo, I'm a huge fan. Thank you for your consistently great content. I have a question, can i also use the old "Indirect" function to achieve this? Where in the validation, list, =Indirect(D5). Also the categories would have been made named ranges..Bars, Bites etc? Hope you can help 🙏🏿

[@chandoo_]

You can, but I no longer recommend it (as you need to first create all the names too). If you have Excel 365, use the trick I've demoed in this video.

[@Excelingtheeasyway]

@chandoo_ Thank you. I Do have 365. So I'll study this vid again...thank you again Uncle Chandoo 😀😀

[@rubengao1508]

Thank you for your video. Is there a way to remove the blank rows in the data validation?

[@chandoo_]

You can by using an OFFSET formula instead of XLOOKUP. But probably not worth the hassle.

[@rubengao1508]

@@chandoo_ a fair tradeoff. One blank row for the convenience of XLOOKUP embedded in the data validation.👍

[@MiniMomentsDesh]

Can you do this without using xlookup function , are there any possibility to do this same thing using index match function?

[@chandoo_]

You can... Use this formula - =INDEX($J$5:$N$15,,MATCH(D5,$J$4:$N$4,0))

[@arclightimages6293]

Where in your other "Conditional Formatting" video do you discuss how to highlight STALE data?

[@melissabaldwin1186]

Thank you so much, super helpful and so much easier than using the INDIRECT option. Is there a way to remove the blanks though in the drop-down for the second dependent list, that may not have have as many options as the others? (for example, Bars has a larger list than Bites, so how do we prevent the bites list to show so many blanks? I saw in your example that those "zeros' only resulted in one single blank, but for some reason mine results in many blanks). My list keeps defaulting to the blanks so unless you scroll up the user wouldnt see everything included in the dropdown list. Thanks!!

[@chandoo_]

You are welcome :) Excel should automatically remove all those excess blanks (barring one). I suggest updating Excel or waiting a few months instead of writing complex formulas that remove the blanks. But if you want to go down that route, you can use OFFSET() with XLOOKUP and calculate how many items are there in your list (with COUNTA).

[@himanshugoswami3256]

Hii.. Love your videos💕 I HAVE A QUESTION in data validation how can I put a custom cell (eg.- If I have 5 products I will put that in a list that I want to validate but I want one extra option like whenever I put "others" I can write in that)....HELP!!!❤

[@johnborg5419]

Thanks Chandoo. Can you make similar with named ranges and using the indirect function??

[@ronaksuvarna1432]

Great Video , but how can we extend these to 4 levels or 5 Levels . Can you please let me know @chandoo

[@chandoo_]

The process is same, you just need to layout the data. But I recommend limiting it to 3 levels and changing the data set up for your sanity 😊

[@MashakaMalalo]

Awesome explanation. Thank you

[@chandoo_]

Glad it was helpful!

[@Kunalupotdar]

Awesome video followed by awesome T Shirt :)

[@chandoo_]

Thanks so much!

[@roberttaylor3594]

excellent t-shirt!

[@CAKPRashique]

Excellent chandoo

[@vantelasunilkumar2173]

it is applicable for the google form , can you share the link , it helpful me

[@Im_Dheena]

Can anyone help me understand on the last point on how to go with the conditional formatting. Couldn't find it out.

[@rakeshmvora]

Dear Chandoo Sir… can you please make same kind of function idea for Microsoft 2019

[@lpanades]

Can I get these values from a table like I get using filter command? I will explain better: I get a table with the collumns: category; product; product size. I can choose the category and show only the producs from that category in the next drop dow and in the othe next I only wanna show the sizes of that product. And this can follows how long desired.

[@chandoo_]

As of now, Excel data validation needs data to be present in a physical range or from a formula that can return ranges. So that means you have to use XLOOKUP (or OFFSET). FILTER doesn't return a range. It returns an array, so we can't use that as DV formula.

[@johannadoyle4810]

Hi, typed in the data as above on a seperate sheet naming it DATA. Entering formula =xlookup(D5,data!$A$1:$E$1,data!$A$2:$E$12), but error tells me "VALUE NOT AVAILABLE .....". Any help troubleshooting this problem would be appreciate, many thanks

[@thevaliantrebel4338]

Having a similar problem but in my case it returns a value if i only select 1 row but if i selct them all well i get the value option

[@unnikrishnansanthosh]

cool solution, thank you for sharing

[@wariojosh]

Hello, is it possible that the Source in my List Data validation doesn't accept a fonction?

[@OzDesi]

Hi Chandoo, This is great. Is it possible to do this in google sheets?

[@luthfinugraha485]

Does it applicable to Google sheets as well?

[@bindui5589]

Sir,please explain about semantic model

[@Hamari_roz_ki_kahani]

Can it e done by hlookup in older versions of excel

[@jacklynrochellealla7717]

new sub here, so cool!!! Thank you

[@chandoo_]

Welcome!

[@shahbazraza8605]

Filter formula is a better way to create dynamic depend drop downs, while using XLookup we have to first arrange our data which is time consuming

[@chandoo_]

You can't use FILTER formula inside Data Validation rule. So it must be set up outside for every combination. Filter returns an array, while XLOOKUP returns a range. Data Validation formulas need either a physical address or a formula that can return the range.

[@gauravmaurya2125]

Amazing

[@Karthi_Max]

Could you pls make the video for calendar drop down while entering date? If it is made earlier, plz share the link. Thanks

[@chandoo_]

Excel desktop doesn't have the calendar option and they are not easy to implement. I think Microsoft has plans for Excel on the web to show calendar for date fields.

[@Karthi_Max]

@@chandoo_ yup. I'm using calendar for date fields in web. But I'm looking forward to the app version🥲

[@BigCliq]

Unrelated: I have a SQL question... I hope you can help out ... Inserting data into ms SQL server, theres an error response saying I can't insert more than 1000 rows... Please do you have a Syntax to help insert more than 1000 rows? Thanks.

[@stanTrX]

Thanks. What is the latest trick?

[@thatdogisbruno]

Do you have a video on this that does not use xlookup? So that it can be done w/o excel 365.

[@chandoo_]

The approach is same. Instead of XLOOKUP, use this formula in Data Validation screen. =INDEX($J$5:$N$15,,MATCH(D5,$J$4:$N$4,0))

[@JerryIwuogu]

Hello Chandoo please help me out here, I am trying to do this on google sheets, but I keep getting an invalid error response anytime I try entering the formula

[@mohammedzabiulla4787]

Hi, how to do the same work in Google sheet ? pls help

[@veluji8710]

"You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria" =N7:INDEX(N7:N35,LOOKUP(1,--(N7:N35""),N7:N35))

[@chandoo_]

Double check your formulas. Type it in a cell and see what it returns.

[@rajn2079]

Excellent❤

[@edwinyulianto3660]

Thank you.. thank you

[@Vikashkunal2011]

Name range you specified cannot be found, getting this error

[@Saad-Sadiq]

Can we use Index/Match if xlookup functionality is not available?

[@chandoo_]

Yes, you can.

[@JohnAdair-k7j]

Fabulous!

[@johanverkerk3260]

Thanks Chandoo, If you expand the table, I don't think it will work. Or is that even possible? Already found, give the data a name.

[@chandoo_]

You can easily scale this by applying a dynamic named range idea on top.

[@johanverkerk3260]

@@chandoo_ I have done it and it works great. Thanks

[@skv4611]

Haven't noticed pushpa till the end...😊

[@priyaram7274]

Where in your other "Conditional Formatting" video

[@showtime1878]

How to use the conditional formatting? The next video doesn't show

[@chandoo_]

See this for an example - chandoo.org/wp/data-entry-forms-with-conditional-formatting-and-validation/

[@abdelazizallam]

Thanks a lot!

[@rajeshmajumdar4999]

Thanks

[@raaj8474]

What if the data is in separate Tab ?

[@d3v1lsden]

It's not working for me, my data is in another sheet, I tried the same method step by step and I dont know what's wrong, its not working out.

[@UKJ28]

Please make searchable dependent drop down list

[@chandoo_]

Search is now a default feature of Excel drop downs. You don't need to do anything. Just type in the cell and it filters the list down (works in Excel 365).

[@rksingh8898]

I am getting error,"This entry leads to an error.Try entering different values to continue."

[@LiserylarineImaculata]

why it's not working to my 2nd row huhu

[@grendbelema]

nice

[@mrvrkram]

Unable to play the video

[@Kalicharan2737r9t]

1000 likes🎉🎉🎉

[@NamzHome]

Nice but this can be achieved easily using the Indirect function.

[@chandoo_]

I disagree. Indirect requires setting up either named ranges (which is manual) or sub-optimal formulas.

[@DickvanderVelde]

Agree, and with indirect it can be dynamic. So you can add categories and you don’t need to update formulas. If you look for column numer and then use that. So indirect works better.

[@opsgoswami]

ITS NOT WORKING IN MY EXCEL.... SOMETHING I'M DOING WRONG... BUT I DON'T KNOW.

[@BenDC85]

Ok, but what if I don't know what I want? Bars, Bites... All I know is that I want something with peanut butter, and I don't want to search through all of the categories.

[@chandoo_]

In that case, you can use my 2-level validation drop down trick - chandoo.org/wp/two-level-drop-downs/

[@BenDC85]

@@chandoo_ Awesome, I will check it out. Thanks!

[@老番新闻粤语]

🎉

[@dbkpbk]

But chandoo it's not dynamic

[@Akshay-if5im]

I am getting a warning --> this type of reference cannot be used in data validation formula😢

[@ganesh6283]

We can use indirect formula instead of xlookup😂

[@chandoo_]

You need to have either names or calculate the range address for INDIRECT option. I no longer recommend INDIRECT for this purpose as it is too manual and clunky.

[@kaushiksrivatsa]

First view

[@EvilEyeTheEye]

Your video title should have been "How to create a simple Drop Down List", not "Dependent Drop Down" or any other click bait BS....

[@chandoo_]

Did you even watch the video. It is about dependant drop-downs.

[@naseerhussain6080]

Tried to create this xloopup function in browser but it says this entry leads to an error @chandoo

[@chandoo_]

I am not sure if this concept is available in browser version of Excel.