Searchable Drop-Down List in Excel - The Easy Way

hello and welcome to this video tutorial from computer Garga comm and in this video we will look at the easiest way to create a searchable drop-down list in Excel so we can use the drop-down list in its normal sense I can grab a name from a list here or I could begin to type something such as s IE m and i use the drop-down list it has filtered to only show those options with my search criteria i have done a video on creating a searchable drop-down list before back in 2017 using multiple formulas but now with the filter function things got a whole lot easier so i'm going to show two examples of a drop-down list here for the first example we have this client on this invoice and i want to enter a name from my client list from this names sheet it's a reasonably large list so I'm going to make it searchable now beginning with this names sheet the first thing we need to do is identify which of these names meet the criteria of the search and I'm going to do that using a function called search so to make sure all happy with is to begin with in cell c2 I'll use this search function which returns to position of the characters we're typing now we ultimately don't care about the position but the fact that it finds them it confirms that their present is what we do care about so if this search function the find text will be the text that is entered from a drop-down list so if I come back over to the invoice sheet and select the cell which currently doesn't have a list that ultimately will do then I'll put in my comma sort unprompted for the texts within now that is going to be the list of names these names are arranged in a table they're formatted as a table which is called notes so if I select that column I'll get a table called names in the column called notes that will make it dynamic so if the table grows while forming or happily pick that up a comma then asks me for where to start searching from I'm going to search from the beginning of the name to search the entire characters so it's kind of a redundant question but I'm going to enter a number one anyway start from the first character of those cells close bracket press Enter if a press Enter I've got my dynamic formulas working in this version of Excel so it will shoot to the bottom of this list of names confirm that at the moment everything is present but if I now go over to the invoice sheet our top something in such as M I and press Enter when I come back over to names I get these errors but as I scroll down the list look at that there is a name here with MA in it so returns to position of MA in that list and as I scroll down I can see there's a few there or beginning with ma as the first character so if it doesn't contain those characters mi error message if it does the position of them let me scroll back up to the top so as mentioned we don't really care about a position but we do have a way here that it's identifying whether they're there or not what we now want to do is convert that into something usable such a true/false so let me come back to that first formula and I'm going to add a function called is number around what we have so if there's a number return true if there's not return false so now when I add that the job is done I've got trues and falses scrolling down to that name from earlier I can now see true now we need to filter it and this is where our filter function is coming in if you're new to the to function I have a link in the description of this video to a video I've done all about the filter function it's a dynamic array function that is only available for people in office 365 so you really need to be on office 365 for all the new features that is coming out in Excel this being one of them going back to that first sale I'm adding the filter function it's going to prompt me for the array to return that is going to be the list of names from a name table comma what to include that is what we have already created the ones that meet that true/false criteria on the end of this I can put my comma I can see the prompt underneath telling me where I am all points when you're writing larger formulas like this what do you want to do if the filter function returns nothing if nothing meets your search criteria I'm going to put an empty string as in return no names you may decide on something else here so I've returned all the names or to return strings such as nothing found I'm gonna choose to do nothing closer for my bracket and press ENTER so only the names that include the letters ma are being returned now it's time to go back over to the invoice sheet and create a drop-down list so if company got there's a sale will send ma at the moment if I just remove that come into my data tab data validation on the settings tab on to allow a list and the source for this list is back to the names sheet or click in the very first name mentioned cell c2 and then putting that hash tag with a pound sign to reference a spilled range a dynamic array technique there so does about how big or small with this list is it's going to be everything from c2 to the bottom of it if I click OK I have the drop down list and if I go to use it if I type si and you my drop-down at the moment I get this error now this is caused by something in our data validation settings so that we can't saw this go back into data validation and when you create in searchable dropdowns like this we should go and turn off the error alert and we can do that with this little checkbox here show error alert on invalid data if I click ok now I'm going to use it yes I drop down it's working successfully and it's returning all the names where si is included doesn't care where the si is included where its first name or last name or somewhere in the middle for example Isobel there they get returned and if I start adding more so if I go into that sale and put B in as well si B then Isobel only is returned where for a move it and put Jie and try that then we've got five names of j8 so here we have our searchable drop-down list very easy to do just one column of free functions search is number and the excellent filter function now let's look at another example to make things a little bit more specific now for an example like the previous one searching for names the fact that it was searching anywhere in their name was possibly quite useful because we could search over the first name or the last name depending on how we wanted to try and shorten the list of finding individual but for the next example I want to drop-down list for countries and if we go to a sheet called countries I have a list of you guessed it countries in a table called countries and we want to filter this now in this example I have personally can't see much in the way of benefits of searching anywhere in the list if on Canada I'm going to type see a just like when you type in formulas in Excel if I go to type of formula and put because su it's all ones that begin with su is not searching for letters su anywhere in their name and that's to behavior one of this searchable list so let's start by filtering the names like we did before I'm going to use the search function again now it's probably worth mentioning that is also a function called find and the only difference between search and find is that find is case sensitive now I'm going to ignore it in my example I want to use search but that might be something you're interested in to force the case that people type in as well but I'm going to search to find text is over on the invoice sheet this time the country cell cell d6 comma where are we searching for it back to the country sheet the country column of the countries table come our stop for number one close bracket we know that's going to be dynamic and return these numbers let me go back in and add the isn't a function no you don't have to do it in this iterative process like I'm doing you're confident you can just write it straight out with filter but this is just how I want to demonstrate to you right now his number goes in we now have the true some falses and then if I add in my final step for the moment just to repeat what we did before and Froemming the filter function to return the country comma or to include what we've written and on the end the empty string so at the moment exactly the same as before there's my countries but come over to invoice and the type of C ie and let's come over to countries it's filtering for the CA and we can see that Dominican Republic and South Africa and Jamaica are coming in and I don't want them the only ones that makes sense in my eyes Arkham Bobadilla and Canada knows that begin with CA so that's where formula and that's fine tune little bit more where we have the search and it's searching from the value from cell d6 in countries around that I'm going to use the left function I want to intensely search just from the left hand side of those names the text will be what we've got countries comma the number of characters it depends what of types of type C a but a critter type CIN so I'm going to use the Len function to return the number of characters that were typed Open bracket where's the text I'm going to copy what I've got here I could just click on the other sheet but I'm gonna copy this invoice d6 and just paste it in my Len function and close off the Len function then close off the left function and that is what we want if I press ENTER now it's only the countries that begin with CI let's pop back over to the invoice sheet and finish this off by adding in the data validation list data validation let's remove the error alert over to settings list and the source back to countries clicking the first cell and put in our hashtag for the dynamic spill reference and if I click OK save I type C ie and use my drop-down there we go if I type II N and use my drop-down I have nothing but if I put s P I drop down and I'll get Spain so I can see that is working how I want it let me put Spain in there so to search will drop down this there using the filter function along with other text functions such a search find Len left to get what we wanted it is so easy nowadays because of that filter function I hope you found this video useful please check out some of that other tutorials on our YouTube channel and come check us out at cúcuta Gaga calm you

Channel: Computergaga

Views: 60,375

Rating: 4.9325843 out of 5

Keywords: searchable drop down list in excel, microsoft excel, excel drop down list, search within dropdown list, dropdown in excel, searchable, excel data validation list, excel searchable list no vba, excel dynamic drop down, excel dynamic array, dynamic arrays, excel tutorials, excel search function, isnumber, excel sort function, excel 2016, excel 2013, excel 2010, advanced excel tricks, excel tips and tricks, excel filter function, computergaga, excel online course

Id: Ea_ACp5W8zI

Length: 13min 42sec (822 seconds)

Published: Tue Jan 28 2020