Create a searchable drop down list in Excel

in this video I'm going to show you how to create a searchable drop-down list so I use data validation drop-down list all the time you know this thing where you can use data validation to choose something from a list but brilliant absolutely fantastic so this drop-down list here is created from these cells over here just using a simple data validation I'm not going to show you how to do that I'm sure you already know it here's the problem if you've got a very long list sometimes it can be difficult to find the value looking for this isn't particularly long list but imagine there was a thousand names on here this has made even harder if you don't know exactly what you're looking for imagine if I'm looking for somebody Winfrey I don't know their first name I'm looking for somebody Winfrey in a list which is sorted by first name and that's going to be really difficult oh there they are now this list initially looks the same yes just a drop-down list but this one's different this one I can type in and once I've typed in there it's going to narrow my search to just looking for the things I'm interested in from a bit more wide-ranging it's going to look for all of the names that contain the word in this case John so Boris Johnson Elton John etc etc whatever I type in here it's going to look for all of the names that contain that particular letter making it much easier to find what it is I'm looking for especially if you've got a thousand names so how do I do it I'm going to get rid of this and start again the good news is here I didn't use any VBA I didn't use any array formulas I didn't use any ActiveX components this is all just done with data validation and some old fashioned formulas first Formula I'm going to use is the search formula I'm going to give myself target so equals search the fine text I'm going to look for is this in d2 which our lockdown base in the f4 key keyboard shortcut with in-text g2 and close that off it doesn't find it John within Angelina Jolie so therefore it turns a value but if I copy this one down you'll see that some of these Boris Johnson does contain John so it returns a number returns the number seven incidentally because it finds it in a seventh character position so one two three four five six seven it finds the word John actually I don't care whether it finds it in the seventh the second or the first position doesn't really matter I'm only interested in whether it finds it at all so I can turn this from a number and value into something that's returns true or false by using the is number function so is number wraps around there and then just turns this from values and numbers in two falses and throughs I can check and see if this is working for the moment I'm looking at for John's which content is true if I look for a different search term you'll see that changes and key is found in Ban Ki moon and Jackie Chan so that seems to be working so far I'll take this a little bit further and turn these pulses and truths into some other numbers by using an if wrapper I'm putting an if at the start of that I'm going to change these into to begin with ones and zeros so you can see now I've got one zero now getting closer to what I actually want why actually one in terms of this is not one one one but I want one two three four yeah now so I want this to increment every time it finds a match for my text now there's lots of ways of doing this my preferred method is to change this from a 1 and use the max function using the max function here and I'm going to check the range between H 1 and H 1 and I'm going to lock down the first of those h1 spacing f4 so that the first one is locked the second one is not and then when I'm looking at that range I'm going to add the number 1 ok so what does that do what this means is if I just also was down just one position for looking at it here it says okay if this is true which it is then give me the max of this range so everything in this range here which evaluates to a zero and then add one so therefore it gives me the number one when I ought to fill down a little bit further if I look at this cell here it says if this evaluates to true which it does give me the max of this range here the max of that range of course is one and one to give us two and so on and so forth all the way down give me every time it finds a match it increments up by one now we're getting somewhere I'll move this column along from where it is at the moment to the other side because I want it to be on the left or leftmost that should give you a close to the next function I'm going to use and and we're then going to use this column to create a dynamic list over here in column i before I do that I just want to use show you another function which you're going to use as part of this the rows function equals rows and then this basically just camps them the number of rows in it given array so if I start this in Excel I won and now sorry I to which is this one on like curly in through to I 2 and then lock the first of those I twos down raising at four and enter that it just says with I to - I - that's one row high if I follow it down this one says I to to I 3 that's too high etc etc this is a really useful function in order to get yourself some incremental numbers it's not useful on its own and of course it's only useful if you use it in conjunction with other functions and I'm going to use a some of you probably guessed the vlookup function here so I'm going to wrap a vlookup round here it uses this incremental row series here and it uses this table across here and it uses the second column across and it's an exact match like so fill this one down it gives me all of those people who's in this case name contains K I if I just check and see if this is still working that's simply working fine I'll tidy this one up by putting an affair a wrap around there I'm going to just say if that's an error give me the empty cell which is semi co-owned invert commas inverted commas and that just needs that up a little bit now very nearly there now with a dynamic list whatever I type in here it's going to give me a list of all of the names that contain that text problem I've got now is that I don't know how many cells this is going to be yeah if I type in B it's one two three four five six seven eight nine if I type in Tiger it's two if I tag it type in John it's four etc etc so I need to basically know how many cells going to be in this range an easier way of doing that is by using count if count if and the range I'm going to look at is I - - I whatever and the criteria for this is I just want to look for when it's got some text in there so a nice easy way of looking for some text is to use inverted commas question mark Asterix inverted commas so that's just basically saying is equal to something of a text string so that's going to evaluate to the number four as I'm changing this that will change automatically now when you use this as part of the offset function so offset says tell me where to start this particular range and I'm going to start it in I two which is the top name how many rows one so therefore I just going to use another comma because it's going to default to one how many columns one again so I'm just another comma because that is the default and then Heights this is the one that I'm interested in the height this is where I'm going to use that count if that we just saw a moment ago that just says count if I - - I whatever and then the criteria is this question mark Asterix which are put in inverted commas so I'll make all of those references absolute and then finally the whittlin so if I just leave that one blank because that will just default to one again that's which is fine now this function isn't any use on its own I actually well it's going to return in terms of this is a range of cells in this case it will return the rate this range of cells it's going to be really useful to put this as a named range I'll show you how it's done first of all I'm going to copy this formula to the clipboard I'm going to go to the name manager and create a new name called validation list I'm going to paste that formula in what refers to so I can check this is working by clicking onto this name and then using this little button here and you can see at the moment it's choosing these two cells so the named range validation list refers to these two cells however if I change this and then check it again now it's referring to these four cells in other words the range itself is dynamic dependent on how many names are in that list so why is this useful this is useful because now I can use this as the range in my data validation so if I go to data data validation use away from a list the source only use one of my named ranges gives a shortcut here f3 to bring up that named list using a validation list okay and okay right so now Mike this is in place I can use this to pick from it and I can type in something here and now here's a problem I can't actually enter this in because data validation itself is restricting me from doing it so what do I need to do to go back in here data validation and the error alert I need to turn this message off so now this means I can change this value use the drop-down list which changes these cells here which changes the drop-down itself and gives me just my filtered list so that's how to create a searchable drop-down list I should point out that I've got a lot of the ideas from this from your Excel nerd which is a YouTube channel that you should check out it's got some great stuff on there anyway I hope this has been useful thanks for watching

Channel: Neil Firth

Views: 1,521,293

Rating: 4.8907733 out of 5

Keywords: Searchable, Excel

Id: vkPoViUhkxU

Length: 12min 7sec (727 seconds)

Published: Sat Nov 03 2012