In this tutorial, you’ll learn how to create a searchable drop-down list in Excel – i.e., a drop-down list that will show the matching items as you type. Below is a video of this tutorial (in case you prefer watching a video over reading the text).
Searchable Drop Down list in Excel
For the purpose of this tutorial, I am using the data of Top 20 countries by GDP. The intent is to create an excel drop down list with a search suggestion mechanism, such that it shows a drop down with the matching options as I type in the search bar. Something as shown below:
To follow along, download the example file from here Creating the searchable drop-down list in Excel would be a three-part process:
Step 1 – Configuring the Search Box
In this first step, I will use a combo-box and configure it so that when you type in it, the text is also reflected in a cell in real time. Here are the steps to do this: (Cell B3 is linked to the Combo Box, which means that anything you type in the Combo Box is entered in B3)
Step 2 – Setting the Data
Now that the search box is all set, we need to get the data in place. The idea is that as soon as you type anything in the search box, it shows only those items that have that text in it. To do this, we will use
Three helper columns. One dynamic named range.
Helper Column 1 Put the following formula in cell F3 and drag it for the entire column (F3:F22) This formula returns 1 when the text in the Combo Box is there in the name of the country on the left. For example, if you type UNI, then only the values for United States and United Kingdom are 1 and all the remaining values are 0. Helper Column 2 Put the following formula in Cell G3 and drag it for the entire column (G3:G22) This formula returns 1 for the first occurrence where Combo Box text matches the country name, 2 for the second occurrence, 3 for the third and so on. For example, if you type UNI, G3 cell will display 1 as it matches United States, and G9 will display 2 as it matches United Kingdom. The rest of the cells will be blank. Helper Column 3 Put the following formula in cell H3 and drag it for the entire column (H3:H22) This formula stacks all the matching names together without any blank cells in between them. For example, if you type UNI, this column would show 2 and 9 together, and rest all cell would be blank.
Creating the Dynamic Named Range
Now that the helper columns are in place, we need to create the dynamic named range. This named range will only refer to those values that match the text entered in the combo box. We will use this dynamic named range to show the values in the drop-down box. Note: In step 1 we entered DropDownList in the ListFillRange option. Now we will create the named range with the same name. Here are the steps to create it:
Step 3 – Putting the VBA Code to Work
We are almost there. The final part is to write a short VBA code. This code makes the drop down dynamic such that it shows the matching items/names as you are typing in the search box. To add this code to your workbook: That’s it!! You are all set with your own Google type Search bar that shows matching items as you type in it. For a better look and feel, you can cover cell B3 with the Combo Box and hide all the helper columns. You can now show off a little with this amazing Excel trick. To follow along, download the file from here
If you have enjoyed this tutorial, I am sure you would like the following Excel tutorials too:
Dynamic Filter – Extract matching data while you type. Extract Data based on a Drop Down list selection. Creating Dependent Drop Down Lists in Excel. The Ultimate Guide to Using Excel VLOOKUP Function. How to make multiple selections in a drop-down list in Excel. How to Insert and Use a Checkbox in Excel.
Hope you understand my question. English is not my native language Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub But i am struggling with one thing. My Combo.text does not display the selected ite item in the list. Don’t know why I would like to be able to find the cell depending the names I’m typing in the B3 combobox. Example, I type this : house, cat OR cat, house => I must have as option in the dropdown list the F3 and F5 cells. (the order of the names must be able to be exchanged) Is it possible ? This method only allows the results to be inserted into one cell. What if this is required for data entry and each result must be in a different cell? Awaiting your feedback I tried out another tutorial about dynamic array, but failed to combine it with the multi-select code I found in your other article (the one with repetition). No matter where I put the “application.Calculate” in that code, I get the result to be, e.g. “int; business intelligence; int; business intelligence” if my search word is “int”. Please let me know how to tweak my code. Thanks for your wonderful guide i want more combo boxes one after another with same functionality. you can consider that i am making a recipe and i will choose ingredients one by one in each combo box. how can i copy the whole formula. please guide. thanks in anticipation Thanks, allimere allimoor sandall random if i enter the search text all i only want to see – allimere and allimoor but your example will also show sandall as it’s finding the search text “all” anywhere within the word. Can i set this to only check at the start of a word? thank you. we have more than 90 product with different type of packing packing with each product. So can you help me to provide excel file where i can choose one product with searchable drop down menu . and provide me your email id i ll send our product list The final part is to write a short VBA code. This code makes the drop down dynamic such that it shows the matching items/names as you are typing in the search box. To add this code to your workbook: Right click on the Worksheet tab and select View Code. In the VBA window, Copy and Paste the following code: Private Sub ComboBox1_Change() ComboBox1.ListFillRange = “DropDownList” Me.ComboBox1.DropDown End Sub Cheers, Agus Thank you. Ive tried creating a new workbook from scratch and I’ve tried downloading the example and then inserting by data in it. In my workbook I have separate worksheets for say banks, nursing homes, and funeral homes. There are 100-200 of each of these. IN each worksheet I wanted to use this option, BUT I can not get the vba portion of it to work. I setup every thing and it looks fine. when I type in the combo box I can see it narrowing the search in column H BUt in the combo box I do not get the interactive list and the option to select the sorted names it just keeps showing the entire list. Any suggetsions? Is there a way of doing this in a normal cell and not a floating one? I want to add the result into a vlookup formula for which i need to refer to rows and columns. Thank you, Ben Image 1 shows what the ddl looks like before using it. Image 2 shows what it looks like when i type in ten and hover my mouse over the 2nd item (1015) Image 3 shows what it looks like when i CLICK on the 1015 — notice that it put i1015 in the ddl but 1015 (that’s correct) in the bound cell to the right Image 4 shows the data — (column a is the data for the ddl) — (columns b,c,d are done to the instruction set shown at the top of this web page) — notice it picked out the two values that have 1015 in them — there is an i1015 about 70 rows down that is not shown n image 4 Image 5 shows what the ddl looks like … …when i click ONLY the down arrow on the ddl …and am careful to NOT hover over anything BUT the down arrow — i1015 is selected =====» Why does it select the 2nd item from image 4? it does not behave like this for any other selection. See images 21,22,23,24 – it does not do that for other items. Just type in ten then use mouse to select the item you want 1010 has 1010 and i1010 – works fine – click 1030, shows 1030 in DDL and bound col 1030 has 1030 and i1030 – works fine – click 1030, shows 1030 in DDL and bound col 1015 has 1015 and i1015 – problem – click 1015, shows i1015 in DDL and 1015 in bound col Thanks for writing this article – it was very helpful and I have performed the instructions you outline above and everything works fantastic! During my testing I ran into a snag. While testing it, I noticed that if I type in ONLY 1 CHARACTER and that character happens to be the first character of the first item in my list of data…. it populates my DDL with the entire first value in the data list and then only shows me other data values that match that. [see images 1 and 2] /////// For example my first 5 values are: 1010, 1015, 1020, 1030, 1040 [see image 3] (and there are 80 more values [for future reference… the value in cell 80 is i1010 …] ) [see image 4] There are more values that begin with the number 1 [see image 3] but I’m just listing the first 5 See the attached images… – When I type in a 1 into the DDL [see image 2] — it immediately puts in the DDL 1010 with the 010 being highlighted in blue [see image 2] and — it drops down the DDL and shows me 1010 and i1010 [see image 2 again] — BUT as you can see from the actual data list [see images 3 and 4] there are other data values that have a 1 in them — What I don’t want is for the DDL to be populated with the entire item it found (thus limiting the search results to what EXCEL decided put into the DDL) [image 2 is what I don’t want it to do] — I was searching for 1 … — but my DDL list has been filtered to all items that have 1010 in them [see image 2] I would like the DDL field to remain empty except for what I typed into it. All I typed in was a 1 so that should be the entire search term. Thanks for any help you can provide. H (NewVersion of comment due to incorrect images uploaded.) – Creating A Combobox Containing Only Unique Distinct Alphabetically Sorted Values – Sometimes unique values need to fill the combobox and need to sort alphabetic..In this way, the processes may be easier.The cells in Column A were selected to fill combobox in this example : For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row – “1” in the code indicates Column A. Also,data is filtered with combobox and copied to the other pages in our study. Details and for example file :https://merkez-ihayat.blogspot.com.tr/2016/07/creating-combobox-containing-only.html Is there a way to speed up the queries for a large column of items? I’m setting up something that uses a 45,000 row list of items and I’ve noticed that I have to wait five or seconds for each keystroke that I enter in the ComboBox. Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub However, sometimes, when I click on a combo and start writing the name of the country, the size of the box starts getting bigger and bigger the more I come back on it and click on it, or the size of the font starts getting smaller and smaller the more I come back to the same box to change what I have written. Would you have an idea why this is, and how to solve it? Is there something to add in the VBA code maybe, in order to lock the size of the box and the size of font? Thanks in advance! 🙂 It shows how to have multiple drop downs without repetition. Thanks for the detailed tutorial it is really helpful. Few issues while working on the drop boxes I have created two drop boxes “ComboBox21”, “ComboBox22” and used the above macro twice Private Sub ComboBox21_Change() ComboBox21.ListFillRange = “DropDownList” Me.ComboBox21.DropDown End Sub Private Sub ComboBox22_Change() ComboBox22.ListFillRange = “DropDownList1” Me.ComboBox22.DropDown End Sub Now the problem is if I select value on one of the search drop down box it doesnt show dropdown suggestions while entering the values in second drop box. and open up the filter of the first drop box. Appreciate if you can provide a solution around that. If required I can send my file for your review This tutorial worked fine for me. I have one question only. Is it also possible to make a restricted list of this? For example the list to choose from is: Netherlands Belgium Germany What i want is when i fill in ”England” a popup screen says: This is not a chooseable country. You know what i mean? Waiting for an solution. Thanks in advance. Hope I have answered your Query !! Regards Pratik Parmar Private sub Private Sub Combobox1_got focus() ActiveSheet.Combobox1.ListFillRange = “DropDownList” End Sub ‘—– Private Sub Combobox1_change() Dim lLoc2 As Long lLoc2 = URUNLER.ListIndex ‘check for a valid entry If Not lLoc2 = -1 Then Exit Sub Range(“b3”).Value = ActiveSheet.Combobox1.Value lLoc = ActiveSheet.Combobox1.ListIndex ‘check for a valid entry If lLoc = -1 And ActiveSheet.Combobox1.Value Empty Then ActiveSheet.Combobox1.ListFillRange = “DropDownList” End If Me.Combobox1.DropDown End Sub Firstly this is brilliant; it may be the most useful excel trick I’ve seen on the net. I am having some trouble with it though. I’m not sure why, but when I do anything elsewhere in my spreadsheet (click a button, check a check box or just type in an empty cell and hit enter) the search suggestions drop down of their own accord. I’m not sure what’s triggering it, although I am quite a novice at excel so I’ve probably just made some tiny foolish mistake along the way but I can’t seem to work it out. Do you have any idea what I could have done wrong? Regards MattRNR Payroll 1 Payroll 2 Payroll 3 Payroll 4 Payroll 5 Payroll 1 Payroll 2 Payroll 3 Payroll 4 Payroll 5 …And it keeps looping them inside the search box. Any way to get rid of the extra entries so that it just shows the 5? If you could have a quick look it’d be great but I’ll understand if you’re too busy. Thanks in advance! Thanks for this amazing article – it worked seamlessly when applied to a template I am currently developing. The explanation and code is very easy to follow and implement – so thanks! I read all the comments, and I’m sure nobody brought this up. I am using the dropdown right now as a reference to pull up entries from a separate datasheet using another activex button. The problem I am encountering is that when the button is clicked, it only runs through part of its code. Currently the logic behind the button is structured as: If input (cell that dropdown is linked to) is empty, then msgbox “Please select an activity” (action 1) Else: If input entry matches an entry in list of activities then call function 1 and function 2 (action 2) Else msgbox “Cannot find the activity you are looking for” (action 3) I find that the linkedcell area is always empty once the button is clicked so action 2 never runs. I created another button that just calls function 1 and function 2 (so action 2) and it works, but now the error handling code that I wrote no longer applies (so runtime error every time the cell is empty or if the entry does not match the list). Any ideas why this happening? Much thanks! Amy storename | address | phone # so i used the formula that shows the actual result name, copy pasted into 3 other columns to the left of these, and then i hid the original ones so to only show the resuts after typing in the bar, and i wanted it to give me the results as you see on top so if i have a store i just type the store name and it would show all 3 columns name address and phone #…. but it partially works…. it worked at first when i had 360 rows… then i had to add more stores to the list and then proceeded to modify the formulas accordingly to fit the rows… say i had 380 rows now… i change the formula to row 380 as the end, but then i get an error that it cant work with the formula…. and if i leave it as it was before… it would select more rows than i have… if i have 380 the formula would read like the last row was 760… so it ended up selecting empty rows… if i try to just select the total rows i have it would keep giving me the INCONSISTENT FORMULA error….im confused… all i changed was the total rows in the formula… nothing else … it worked fine before but not now. what do i do ? what am i missing? please help. The drop down works, but its temperamental. when the drop down appears sometimes it stays there even when i click on another cell. in fact when i enter content in a completely different cell the list from the drop down just appears as though it has a life of its own. any advice on this Thank you so much for this tip! Is there any way to make the link cell dinamic? I want to apply this combobox to work in diferent cells from the same column. Also the list of data is in another worksheet within the same workbook. First of all, thanks a lot for this great tutorial and this great idea. I am now working to implement this, such that the combobox is invoked inside of the cell I am presently in, and it works accordingly. I am relying on the Workbook.SelectionChange event for this. However, I am having some trouble in doing this. I believe it is because the With .ListFillRange = DropDownList .LinkedCell = LinkingCell End With is creating some problem. Any suggestions? Have you already got something like this? Also, I am working on reducing the 3 lines of helper columns into a small VBA code (using arrays to handle the data), mainly for performance reasons. I will share it with you as I get done. Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “DropDownList” Me.ComboBox1.DropDown End Sub Just copy and paste answer from Sumit Bansal :d Thanks Thanks a lot! I’m sorry the picture is a bit small but if you can manage to see, the top half shows the selected item in the list and everything is fine. The bottom half shows when I type something else in another field in the same sheet and tab out of that field, the list provides the same selected item in blue again. Thank You for your assistance! DV Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub However I am getting issue When I use arrow keys (down) to move the selection down the dropdown list to select say 3rd item. Only the first item is selected and dropdown list disappears . What I want is , when I use arrow keys(up/down) to move the selection up and down the dropdown list, it should only scroll up/down and it should not change the value of the combobox (and fire the change event) until the user hits enter or tab . Please help. I’m glad you were able to sort it out. I’ve added some screenshots for reference showing the data validation source, various cell formulas and the resulting drop down list. Cheers, Joe Thank you so much for doing this, Lillian My list is very long so when I type in, for example, “a” I get all the records with an “a” anywhere in them. What I really wanted was just those records STARTING with “a”. So I created a new formula in cell J3 which was =LEN(B3). Then I edited cell F3 to be =–ISNUMBER(IFERROR(SEARCH($B$3,LEFT(E3,$J$3),1),””)) and copied down. Any thoughts? =–ISNUMBER(MATCH($B$3&””,E3,0)) I used it in F3 and copied down. Yes thanks, much more elegant than my attempt. Cheers Private Sub ComboBox1_Change() Dim vCombo As Variant vCombo = Me.ComboBox1.Value Sheet6.Cells(3, 2).Value = vCombo ‘ add combobox value to cell B3 End Sub Filtered Dynamic DropDownList is appearing, but I can select only FIRST item from list. If i’m selecting any following items – it’s becoming blank.. Earlier I used the same as yours formulas before. Just without Combo box on sheet. Instead of that I used formula =CELL(“contents”) in cell B3 (referring to your lesson) – with this you will get value to B3 from any cell in sheet you will type in. And I have copied Name Validation to all cells I need. It filters list only after I press drop down arrow, but i was enough for me. Now I would like to create that in VBA form. I’m not professional in Excel, but thinking about possibilities to create that using dynamic arrays and filter them only using VBA code. I need ~30 combo boxes on the form doing the same.. Waiting for you tutorial about it on VBA form.. If you enter CIS into any of the search bars you will see what I am talking about (hopefully). Also, I just noticed when I choose CIS 13 it populates CIS 136 (another choice). Private Sub ComboBox1_Change() ComboBox1.ListFillRange = “DropDownList” Me.ComboBox1.DropDown End Sub sorry I think I keep posting twice, it doesn’t seem to register the first time but then it does. You are great and wonderful working on excel and I want to give you specially thanks and appreciate for your working . I m also teaching advance excel in our local area and I have a question regarding your working “excel drop down list with search…..” Dear I want to this kind of search engine in one particular column for typing data entry, I want to working on active cell and don’t want combobox, creating search engine on each cell with list (Data validation) or through vba… Is it possible?? Hope you are understand what I mean to say.. 🙂 Regards, Mehar Khatri Got focus -> Change Private Sub ComboBox1_Change() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub Great tool you have there, I have it set up and working now! However, I am using this for a list containing quite a few duplicates, which I would like to remove from the drop down list suggestions. Would you have a solution for this? I tried some VBA coding, but I can’t seem to combine it with your method. Any help would be greatly appreciated! Thank you This article is amazing. I am facing a problem when trying to scroll the mouse on the list of suggestions. The whole Sheet moves and the combobox stays on the correct cell, therefore the suggestions roll down the sheet and it’s terrible. Any ideas how to fix it? Great tutorial! I have a slight problem I hope you can help. My combo search box only shows 1 data even tho there should be more more. My look-up table is in columns and not in rows like yours. You mentioned in your video (around 12:30) that since the array only has 1 column, there is no need to add the column number. Could this be the problem? If so, where or how do I add the column numbers? Here’s my formula: =$C$2:INDEX($C$2:$KF$2;COUNTIF($C$2:$KF$2;”?”)) Your help is much appreciated. Great to see your trick here.. if I have many cell to get this search suggestion from dropdownlist, can we do it? usually I use dropdown list with Data Validation (list), and now i’ll try to improve with auto completion, can we do that? many thanks This can be done with multiple drop downs but it becomes cumbersome. If you have more than a couple of drop downs, I suggest sticking to data validation drop down (as they are easy to replicate) I have followed all ur istructions. But when I type in the combobox the 1st letter A then nothing appears in the list. When i type in letter B then all the letters of names that starts with A appears but no B. Have I missed something ?? Hope this helps! I am working on a Vessel Crew program and by finding the crew under last name is now working with this cool function. Although they are not related I have here 3 men with same last name, How can I add a second combobox giving me the opportunity to select the correct person? Thanks inadvance Hope this helps! Not sure if we fully understand each other but to be sure 🙂 I have one table with 40 Columns and 1000+ Rows and I can search key ID like Birthday or Employee number, (This information is not allays available to end user by default) With your help I can also find persons by Last name in Combo Box by looking at “Last Name” (column J) but when i need to find a person that has same Last name as another person i would like to be able to select the correct first name in the same combo box or a different one FYI This search is not on the same Sheet as table but on a separate “Form Sheet”
- I would to get the full list any time I trigger the dropdown unless the user has started typing in a value – even if a value was previously selected. How do I change the ListFillRange to accomplish this (assume I change the Named Range) ? 2. I would like the list to automatically dropdown when the user starts typing in a value in the textbox portion of the combobox. 3. I would like to display additional column(s) in the dropdown for display purposes only to provide more info about the item to the user. How do I modify the DropDownList named Range to include additional columns?
- Is there anyway, I can speed this up. I have a large range of data that gets evaluated when the user manually types in a value. Thanks Steve =IFERROR(INDEX($B$68:$B$81,MATCH(ROWS($G$68:G68),$G$68:$G$81,0)),””) (first name – set 1 row to display) =IFERROR(INDEX($C$68:$C$81,MATCH(ROWS($G$68:G68),$G$68:$G$81,0)),””) (last name) And then, there are two drop lists (drop1,drop2) to engage to combobox 1 and combobox2, and ListFillRange: drop1=$H$68:INDEX($H$68:$H$81,MAX($G$68:$G$81),1) (first name) drop2=$I$68:INDEX($I$68:$I$81,MAX($G$68:$G$81),1) (last name) Last thing, VBA is also the same for each of combobox. I can do follow him. You too 😀 Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub I hope this works for you!! Thanks. -Joe Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub Hope this works for you. Private Sub ComboBox1_GotFocus() ComboBox1.ListFillRange = “=DropDownList” Me.ComboBox1.DropDown End Sub Hope this works!! I don;t know why but when I click on combo Box –> type text (exam: An ) –> chose 1 item in the list ( Exam: An Binh hosp)–> press Enter –> LinkedCell (C3) is ok (=An Binh hosp) but Combo Box.value is blank (properties and appearance on screen) –> click the arrow of combo box, choose “An Binh hosp” again –> Combo box appears “An Binh hosp” in it. Did I miss something when following your instruction ? Here my file: https://onedrive.live.com/redir?page=view&resid=79FB6F76781A1D6A!195&authkey=!AFNZEOKhnwEb6ds Please help me take a look and give me solution. Thank you in advance. BUT- the only difference is when i used to use regular Data Validation list- it was easy to copy that drop down into an entire column. How can i do that with this Active X control Drop down? Because i want to use it for at least a thousand student records… need to line up with the other student data. Here is how you can make named range dynamic – http://trumpexcel.com/2014/01/formula-hack-16-create-dynamic-named-ranges/ Or even better, use excel table feature – http://trumpexcel.com/2014/03/excel-table-the-hidden-treasure-in-excel/ Hope this helps!! You save me with my problem…you are so good in excel and learned so much on your website. I hope to learn more from you! Thanks so much for this amazing website. Is there a way to communicate directly to you…really want to learn more 🙂 Thank you. Janice Nunez. You can reach out to me at sumitbansal23@gmail.com Thanks. I have just send you an email. I hope you will have spare time to read it and would really need some help. Thanks so much. Private Sub ComboBox1_KeyDown(ByVal KeyCode As _ MSForms.ReturnInteger, ByVal Shift As Integer) ‘Disable Up&Down-arrow key – causing problems in ComboBox selections If KeyCode = vbKeyDown Then KeyCode = vbNull If KeyCode = vbKeyUp Then KeyCode = vbNull End Sub source: https://www.reddit.com/r/excel/comments/3zkz8f/a_way_to_disable_arrow_keys_in_a_combobox/ Thanks in advance Date Invoice no. Telephone Name Address Pcs 16-03-14 55544596 Mohamed Al Marri Muaither 1 23-03-14 66633221 Khalid Ibrahim Al Aziziya 1 24-03-14 66553898 Mohamed Ali Dafna 1 I want the “Address” field to come from a search box and the list in infinite… pls see file…
