This blog post will take you through the steps to create a searchable drop down list in Excel – just like Google search.
This is a great Excel trick for working with large drop down lists.
In this tutorial we will use a list of 87 names that as we type into the drop down list, it searches the names, and the list shortens to show only those names containing that string of characters.
There are a few formulas to write to get this searchable drop down list in Excel created. Everything is shown and provided in this tutorial. If you prefer a video. Check out the video tutorial below.
Watch the Video – Searchable Drop Down List in Excel
Modern Excel – Easier Method
If you are using an Excel 365 or Excel 2021 version of Excel, then this much easier method is possible. The dynamic array engine in modern Excel has greatly eased the process to create a searchable drop down list in Excel.
Do not have an Excel 365 version? Please read on or watch the previous video.
Create the Combo Box Control
The drop down list used here is the combo box control. It is not a Data Validation list. So the first task is to insert a combo box control where you want the list to be.
- Click the Developer tab on the Ribbon (don’t have a Developer tab? Right click the Ribbon, select Customise the Ribbon and check the Developer on the right).
- Click the Insert button in the Controls group and select the Combo Box (ActiveX Control).
- Click and drag to draw this control onto the worksheet. You can always move and resize the control later, so don’t worry about 100% accuracy.
- Now we need to link the combo box value to a cell. After inserting the combo box, you should already be in design mode. Click the Properties button on the Developer tab. If this is not active, click the Design Mode button and then Properties.
- The Properties window appears. In the LinkedCell property, type the cell address of the cell you want the combo box linked to.
- Let’s check out what we currently have. Click the Design Mode button to exit design mode. Type into the combo box and you should see the value appearing in the cell you linked it to.
Identify Which Names Contain the Characters being Searched for
To be able to create this searchable list, the first formula will need to identify which names meet the criteria and should continue to appear in the list when the user is typing.
To identify the names that contain the characters being searched for, we will use an Excel function with a very appropriate name – the SEARCH function.
The SEARCH function below can be used.
In this function, cell $G$1 is the cell that the combo box is linked to. So this contains the string of characters that we are looking for. Cell A2 is the first name in the list of names.
This function will return the #VALUE! error if a name does not contain the characters typed, and returns the first occurrence of those characters if it does.
In the image below you can see that the characters being searched occur in the fifth position of the names in A2 and A3, in position 1 of A6 and not at all in the other cells.
Now this is what we need. We now know which names should be appearing in the final drop down list while users are searching.
However, I don’t like the #VALUE! error and I’m not really interested in what position the characters are in. I just need to know that they are there.
The ISNUMBER function can then be added to the SEARCH function so that it returns TRUE or FALSE when a name is identified or not. The double negative is then added before the ISNUMBER function to convert the TRUE or FALSE to 1 or 0.
How Many Names Contain those Characters
The next formula needs to count how many names in the list contain the characters being searched for.
This is important to create the dynamic aspect of the searchable drop down list. As characters are typed the list should shrink, and then expand again when the characters are deleted.
The COUNTIF function is used here to count the occurrences of names being returned. This function uses a reference to the ‘IF Found’ column in B. It has a fixed start point of $B$2, but a relative row number on the end point $B2. This ensures that the reference gets bigger as the formula is copied down.
It is counting how many times a 1 occurs in the range up to the point of the current row.
An IF function can then be added to it so that it only performs the COUNTIF, if the cell contains a 1, otherwise show blank in the cell.
Return the Names for the Searchable Drop Down List
We now know which names should be appearing in the list, and we also know how many there are.
Next step is to create a range of cells containing these names only. A drop down list can then use this range of cells.
To lookup these names we can use the amazing INDEX and MATCH function combination.
The INDEX function is used here to return a name from the range A2:A88 from the row specified by the MATCH function.
The ROWS function is used to return the current row number. So for the first name in the list it is row 1 of that range. The second name is row 2 and so on. This works to provide MATCH with the current name we are looking for (1st, 2nd etc).
The MATCH function then looks for this number in range C2:C88. It reports back to INDEX with the names position, and INDEX extracts the name.
The IFERROR function can then be added to this so that the cell contains blank if there are no more names to look for.
Define a Dynamic Named Range for the Searchable List
Now that we have the returned names, we need an easy way of referring to them. For this we will create a named range. This will need to be dynamic and detect how many names are returned. The combo box will then use this named range for its list.
Click the Formulas tab on the Ribbon and then Define Name. Enter a name for the range (I named it employees) and then enter the formula like below into the Refers to field.
To create the dynamic named range we used the INDEX function with COUNTIF. The INDEX function is capable of returning a value or a reference and in this scenario we need it for the latter.
The dynamic range starts from $D$2 and then the COUNTIF function is used to locate the last name in the list for the INDEX function. The “?*” is used as the criteria to count the number of cells containing text.
Assign the Dynamic Named Range to the Combo Box
Now that we have the searchable list of names in a column and the range has been named. The combo box needs to be told to use this named range as its source.
- Click the Developer tab on the Ribbon and then the Design Mode button.
- Now we are in design mode, click on the combo box control and then click the Properties button.
- Type the name that you gave your named range into the ListFillRange field.
- Close the properties window.
Make the List Drop Down on Change
The final step is to make sure the list drops down when somebody types into it. At the moment someone could click the list arrow to drop it down, but if they type, no list appears.
- Double click on the combo box (you need to still be in design mode for this).
- This will take you to a code window to enter a tiny piece of VBA code. Type the line below in between the Private Sub and End Sub lines.
Private Sub ComboBox1_Change() ComboBox1.DropDown End Sub
Adjust the Combobox1 part to the name of your combo box.
If you are interested in learning VBA then check out our Excel VBA online course. It is a fantastic skill to have if you are a heavy Excel user.
Exit design mode and test out your combo box. You should now have a searchable drop down list to use on your Excel reports, dashboards and forms. Everyone will be asking how you did it.
Angelo Fonseca says
simply fantastic! Really good. Thank you and please keep up with this wonderful work.
Hi Alan, Great contribution!!.
Have a question, when appear two o more equal results inside combobox, in fact have the same name but diferet properties, i can not select second, just the firt. You know why?? There´s solution for this??
Ana Da Silva says
Thank you for the amazing Video, it helped me immensely with a tool I’m currently developing for work.
I have a question thou, I included 2 combo boxes in my spreadsheet, and did the code as you instructed:
Private Sub ComboBox1_Change()
Private Sub ComboBox2_Change()
the problem is, when I type in one combo box, and the other already has a selection, it creates a change event on the first one as well.
For Example, I make a selection in Combo Box 1 then I get to Combo box 2 and the minute I type something in it, Combo box 1 gives me a dropdown list which I have to select again.
How can I fix this problem?
Thank you again for all the help and patience with the questions.
Ben S. Moy says
I have he Same Issue. Does someone have a solution?
I also have this exact issue, does anyone know how we can fix this?
Use this Code. Change the field “DropDownList” below to the name range defined by you.
Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = “DropDownList”
Hope this helps 🙂
Alan Murray says
Thank you, Benison.
I have some users on MAC using office who can’t have ActiveX objects. Is there another way to do this with with the basic form drop down?
It is possible, but I don’t have a tutorial for it.
I can’t get the dropdown box to show more than one line. Can you help?
Does the search box have to be on the same tab?
No it can be on a different tab, just adapt the reference to the dynamic list in the combo box properties.
My drop down box only has one line, but there are several that should be listed, why would that be?
Maybe check the dynamic named range is set up correctly. Also check the Combo Box properties and the ListRows property = 8 and not 1.
i have one problem, searching working fine , but selection only working with first suggestion, can you pls help
This worked fine until I closed and reopened file. Then combo box(es) wouldn’t be able to search again or work correctly – only showing one or some names or the one that was selected upon opening file. I finally figured out why… if you leave a name selected in the combo box when you save and close the file, this problem happens when you reopen the file. Instead, you need to clear all combo box(es) before you save and close the file, or if you haven’t made changes to the file that need to be saved, then close the file without saving. If you have accidentally saved it with selections in the combo box(es), just clear out the selection(s) in the combo box(es) (click in the combo box drop down and press backspace until cleared) and save, close, and reopen the file. Then it will work again, easy! Hope this helps someone else.
Alan Murray says
Thank you Jen for sharing this information.
Doug Stewart says
Alan – this was very helpful for a beginner like me. I’ve followed your method, but I have a unique issue I can’t solve.
I have three combo boxes in use on the same worksheet. I’m using your method to help the user select products from a product catalog, which is housed in an adjacent worksheet. I added three sets of helper columns and have three ranges, one for each of the combo boxes to provide suggestions.
But the combo boxes seem to be interfering with each other. The first one works and provides suggestions successfully, and the other two do not. They don’t return suggestions and also appear to return focus to the first combo box when they are clicked. I expect I need to set up some sort of events to control which combo box is active?
Any suggestions would be appreciated!
How to use this 1 combobox at multiple cells whre data validation is required ? It should be easy with VBA code, but I couldn’t make one… Please if you can help