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
if you do have 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