How to use XLOOKUP to Create Dependent Drop-Down Lists in Microsoft Excel

hi there jamie here today at teachers  tech hope you're having a great day today   today i want to show you how you can create  dependent drop-down list in microsoft excel   using xlookup so what i mean by this is what  i choose in this cell if i go ahead and choose   apples then i'm going to get the apples list here  because this is dependent upon here and when i   choose something here this reflects because this  was dependent upon this one and it's using all   this information on the sheet and i'm not using  any filtered list to do this the other thing with   this method you can take this copy them down and  these are all independent from each other so if i   choose down here bananas i'm going to get to  bananas list and everything will reflect so   let me show you how you can create these dependent  drop down lists using xlookup in microsoft excel so i find using the xlookup function much easier  than the previous method that i showed you using   the indirect function the one thing about  xlookup this is going to be with microsoft   365 subscription on it so if you don't have  that take a look at my other video using the   indirect function all right and the other thing  i just wanted to mention that this sheet that i'm   practicing on here today i'll put the link down  below and then you can just save a copy to your   computer and follow along as i create this using xlookup so the first thing i'm going to do is start

with data validation to get my first drop down  list and my first drop down list is just going   to be based upon these four fruit right here so in  this spot i have a6 selected i'm going to go up to   make sure you're under data and then look for  data validation and i'm selecting it right here   so at this point i need to drop down here and  choose list and this is very simple all i need to   do is i'm going to select my source hit that and  just pick what my source is going to be it's going   to be these four fruit and i'm just going to hit  enter and okay and look at this i have my first   drop down just like that so when i choose bananas  i want the type when i do my next drop down this

list to be dependent upon this what i choose  so i should see the four banana types okay so   this is where i need to start using my x lookup  function and i know i said i wasn't going to do   a filtered list yeah and i'm going to show you the  way i did at the beginning without a filtered list   but i just want to show you a filtered list to  show you how x lookup is working and i'm just   going to show you over here in this spot now if  i put equals in i can start a function just like   this and i can look up x lookup just like this  the other way and i'm going to choose this one   the other way i could have done is hit this and  look it up i'm going to open this right up here   because this can help with your arguments if  you're new to this to kind of go step by step   so what's my lookup value going to be well it's  going to look up whatever i choose here in a6   so i'm just going to select a6 and it goes into  here and then i'm going to go to lookup array   well what's it going to look up well whatever i  choose here it's going to choose the array across   here just like that and now i need your return  array well what's it going to return well it's   going to return everything and i'll just move  this down a bit it's going to return everything   here i'm going to give a few extra spots in case  you wanted to add it to some extra fruit after   and now i have what i need i don't need to fill  in these other two so i'm just going to hit ok   and look at this i have since bananas are here if  i choose this to apples then i get the apples list   so this is this right here this spot over here is  dependent upon here notice it's putting some zeros   in here uh you can quickly get a i will show you  how you can quickly get rid of those if you didn't   want those in this list uh if i click on the  where fuji is because that's where the formula   is actually in that spot if i just go to the end  here and i'm just going to put i'm going to use   the ampersand here and i'm just going to put in a  space just like this and watch and i'll hit enter   and then whatever i choose so if i choose my  bananas the zero is gone out of this now you   could just do a validation data validation just  based on this spot so what i mean is i could go

here and click data and do a list again what's my  source going to be well my source is actually this   spot where the formula is and i could hit enter  and i'm going to need to add something to the   source i'll show you in a second what i mean so  if i go to drop down notice it's only showing one   so i'm going to go back to data validation up here  and put a uh just a hashtag after it like this   and that way it allows me to do the list here  so just something if you're doing that way so   and then this is working if i choose bananas  or oranges and now i have my oranges list just   like that okay so that's using a filtered list  this would be my filtered list but if i wanted   to copy down this i'd have to have a new filtered  list going for each one i don't want that to have   to happen so what i'm going to do is i'm going to  just create this first line using this method here   so in this last spot i can use x lookup again  so if i wanted to reflect this is going to be   dependent what i choose here and it's going to  look at the amounts down here if i hit equals   and i'm going to just type in x lookup just like  this and i'm going to just go through the argument   so what is it going to look up well it's going  to look up whatever i choose in this spot put   a comma what's the lookup array well it's going  to look up to specific type of fruit here and i   put another comma and then it's going to look up  the amount that i have left the quantity and then   i can go ahead and just hit enter now i do want  to point out spelling matters right because if   i drop down and i go to ladyfinger notice i get  an a here well look at this ladyfinger here is   spelt correctly but it's missing the r over here  so if i add my r and go now if i just drop down   here for it if i go back now it will work so just  so you know spelling does matter so now i want to

show you how you can do this without having the  filtered list and so we can copy some of this down   and it will work this right here this uh this  first drop down i could just copy it down like   this and then it's going to be independent from  this one when i choose up here but this is the   spot now i can go straight to data validation and  then add my x lookup formula into the source so   what i mean by this is if i go ahead and go up  to data then i'm going to go data validation   right here and i'm going to choose my list so  i am going to put in my formula using x lookup   so just starting with my equal sign i'm going to  be typing x look up just like that and what were   the steps well the first thing is what am i going  to be looking up well it's going to look up this   right here and i'm going to add a comma well  what's the array that it's pulling from well   it's going to be pulling from this right here  comma and what was the final step it was going to   be the selection of where it's choosing from right  here so now at this point that gives the x look up   right inside this so if i go ahead and hit ok take  a look at this right away i have my drop down list   just like this and on this one i could copy this  down if i make a couple changes i'm going to give

it some absolute values in here by just hitting  f4 so if i go ahead and hit function f4 on a few   of these because i don't want these to move so  you can go to your formula and just click inside   of it and i'm just doing function f4 to get those  dollar signs around it just like that hit enter it   doesn't look like anything changes but what allows  me to do is copy it down now so if i go back   here i'm going to open up this data validation  one more time and just open it up you can see that   the dollar sign is around the a7 too well i'm if i  want to be able to copy this down i need to remove   the dollar sign from the seven here because it's  going to be if i copy it down i want it to be able   to go to the eight but i can leave the dollar  sign in front of the eight so just like this   now if i go and grab this and copy this down  take a look at this now if i go and choose   lemons and then drop down i get all the  lemons and everything is dependent from   these and these are separate now just by  putting the x look up in the data validation   just like that so i hope do you like this tutorial  on showing up how you can do these dependent drop   down lists using xlx lookup i find this is the  easiest way in microsoft excel if you do have this   this function inside your  microsoft excel let me know if this works   for you and if what other methods that you're  looking for or other microsoft excel tips thanks   for watching this week on teachers tech i'll see  you next time with more tech tips and tutorials