Skip to main content

The ultimate guide to searchable drop-down list in Excel

Creating drop-down lists in Excel streamlines data entry and minimizes errors. But with larger datasets, scrolling through lengthy lists becomes cumbersome. Wouldn't it be easier to just type and quickly locate your item? A "searchable drop-down list" offers this convenience. This guide will walk you through four methods to set up such a list in Excel.


Video


Searchable drop-down list in Excel 365

Excel 365 has introduced a much-anticipated feature to its data validation drop-down lists: the ability to search within the list. With the searchable functionality, users can quickly locate and select items in a more efficient manner. After inserting the drop-down list as usual, just click on a cell with a drop-down list and start typing. The list will instantly filter to match the typed text.

In this case, I type San in the cell and the drop-down list filters out cities that start with the search term San, such as San Francisco and San Diego. Then you can select a result with your mouse or use the arrow keys and press Enter.

Notes:
  • The search is initiated from the first letter of each word in the drop-down list. If you input a character that doesn't match the starting character of any word, the list won't display matching items.
  • This feature is only available in the latest version of Excel 365.
  • If your version of Excel does not support this feature, here we recommend the Searchable Drop-down List feature of Kutools for Excel. There is no Excel version limitation, and once enabled, you can easily search for the desired item in the drop-down list by simply typing the relevant text. View the detailed steps.

Create searchable drop-down list (for Excel 2019 and later)

If you are using Excel 2019 or later versions, the method in this section can also be used to make a drop-down list searchable in Excel.

Assuming you have created a drop-down list in cell A2 of Sheet2 (image on the right) using data in the range A2:A8 of Sheet1 (image on the left), follow these steps to make the list searchable.

Step 1. Create a helper column that lists the search items

Here we need a helper column to list the items that matching your source data. In this case, I will create the helper column in column D of Sheet1.

  1. Select the first cell D1 in column D and enter the column header, such as "Search results" in this case.
  2. Enter the following formula into cell D2 and press Enter.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
Notes:
  • In this formula, A2:A8 is the source data range. Sheet2!A2 is the location of the drop-down list, which means the drop-down list is located in A2 of Sheet2. Please change them according to your own data.
  • If no item is selected from the drop-down list in A2 of Sheet2, the formula will display all items from the source data, as depicted in the image above. Conversely, if an item is selected, D2 will display that item as the result of the formula.
Step 2: Reconfigure the drop-down list
  1. Select the drop-down list cell (in this case, I select the cell A2 of Sheet2), then go to select Data > Data Validation > Data Validation.
  2. In the Data Validation dialog box, you need to configure as follow.
    1. Under the Settings tab, click the button in the Source box.
    2. The Data Validation dialog box will redirect to Sheet1, select the cell (e.g., D2) with the formula from Step 1, add a # symbol, and click the Close button.
    3. Go to the Error Alert tab, uncheck the Show error alert after invalid data is entered checkbox, and finally click the OK button to save the changes.
Result

The drop-down list in cell A2 of Sheet2 is now searchable. Type text in the cell, click the drop-down arrow to expand the drop-down list, and you will see the list instantly filtered to match the typed text.

Notes:
  • This method is only available for Excel 2019 and later versions.
  • This method only works on one drop-down list cell at a time. To make drop-down lists searchable in cells A3 through A8 in Sheet2, the aforementioned steps must be repeated for each cell.
  • When you type text in the drop-down list cell, the drop-down list does not expand automatically, you need to click the drop-down arrow to expand it manually.

Create searchable drop-down list easily (for all Excel versions)

Given the various limitations of the above methods, here is a very effective tool for you - Kutools for Excel's Make Drop-down List Searchable, Auto-popupfeature. This feature is available in all versions of Excel and allows you to easily search for the desired item in the drop-down list with a simple setup.

After downloading and installing Kutools for Excel, select Kutools > Drop-down List > Make Drop-down List Searchable, Auto-popup to enable this feature. In the Make the Drop-down List Searchable dialog box, you need to:

  1. Select the range containing the drop-down lists that need to be set as searchable drop-down lists.
  2. Click OK to complete the settings.
Result

When you click a drop-down list cell in the specified range, a list box appears to the right. Type text to filter the list instantly, then select an item or use arrow keys and hit Enter to add it to the cell.

Notes:
  • This feature supports searching from any position within the words. This means even if you input a character that's in the middle or end of a word, matching items will still be found and displayed, offering a more comprehensive and user-friendly search experience.
  • To know more about this feature, please visit this page.
  • To apply this feature, please download and install Kutools for Excel first.

Create searchable drop-down list with Combo box and VBA (more complex)

If you simply want to create a searchable drop-down list without specifying a particular drop-down list type. This section provides an alternative approach: using a Combo box with VBA code to achieve the task.

Suppose you have a list of country names in column A as shown in the screenshot below, and now you want to use them as the source data of the searchale drop-down lists, you can do as follows to get it done.

You need to insert a Combo box instead of a data validation drop-down list in your worksheet.

  1. If the Developer tab does not display on the ribbon, you can enable the Developer tab as follows.
    1. In Excel 2010 or later versions, click File > Options. And in the Excel Options dialog box, click Customize Ribbon in the left pane. Go to the Customize the Ribbon list box, check the Developer box, and then click the OK button. See screenshot:
    2. In Excel 2007, click Office button > Excel Options. In the Excel Options dialog box, click Popular in the left pane, check the Show Developer tab in the Ribbon box, and finally click the OK button.
  2. After showing the Developer tab, click Developer > Insert > Combo box.
  3. Draw a Combo box in the worksheet, right click it and then select Properties from the right-clicking menu.
  4. In the Properties dialog box, you need to:
    1. Select False in the AutoWordSelect field;
    2. Specify a cell in the LinkedCell field. In this case, we enter A12;
    3. Select 2-fmMatchEntryNone in the MatchEntry field;
    4. Type DropDownList into the ListFillRange field;
    5. Close the Properties dialog box. See screenshot:
  5. Now turn off the design mode by clicking Developer > Design Mode.
  6. Select a blank cell such as C2, enter the formula below and press Enter. They drag its AutoFill Handle down to cell C9 to auto fill the cells with the same formula. See screenshot:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    Notes:
    1. $A$12 is the cell that you have specified it as the LinkedCell in step 4;
    2. After finishing the above steps, you can now test:  enter a letter C in the combo box, and then you can see that the formula cells that reference the cells containing the character C are filled with the number 1.
  7. Select the cell D2, enter the formula below and press Enter. Then drag its AutoFill Handle down to the cell D9.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. Select cell E2, enter the formula below and press Enter. Then drag its AutoFill Handle down to E9 to apply the same formula.
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. Now you need to create a name range. Please click Formula > Define Name.
  10. In the New Name dialog box, type DropDownList in the Name box, enter the formula below in the Refers to box, and then click the OK button.
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. Now, turn on the design mode by clicking Developer > Design Mode. Then double click the Combo box to open the Microsoft Visual Basic for Applications window.
  12. Copy and paste the VBA code below into the Code editor.
    VBA code: make drop down list searchable
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

From now on, when a character is entered in the combo box, it will do a fuzzy search and then list the relevant values in the list.

Note: You need to save this workbook as an Excel Macro-Enabled Workbook file in order to keep the VBA code for future use.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Perfect idea for me. But I have a problem with the "ROWS" formula.
I mean point 8.
When I use your formula (in the drop-down list I have nothing entered, as you can see in point 8) in the first cell is "INDIA".
And pick up the cells with the "spilled" error. What I need to change for the formula to work properly.

E1 - India
E2 - #SPILL!
E3 -#SPILL!
E4 - #SPILL!
E5 - #SPILL!
E6 -#SPILL!
E7 - #SPILL!
E8 - India
E9 - Brazil
E10 - Italy
E11 - Japan
E12 - United State
E13 - Francy
E14 - Germany

You also see that there are more poems appearing than yours.
This comment was minimized by the moderator on the site
Hi Przamek PL,
Sory, I cannot reproduce the problem you mentioned. Can you provide us with your data for tesing? If you don't mind, upload your sample file here.
This comment was minimized by the moderator on the site
Thank you for your message.
I was able to run your example correctly.
I have a reflection now ...
How to apply your solution to the UseForm form?

I would like to select a person from the list in the form, then I would have information about the age of this person elsewhere in the form. Such a simple example. Difficult?
This comment was minimized by the moderator on the site
Hi Przemek PF,
This method does not work in UserForm. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Somehow excel will not let me fill in the ListFillRange with ANYTHING. so also not the DropDownList. I did all the steps but am not able to get a flashing cursor and when I type no drop down list appears. any solutions?
This comment was minimized by the moderator on the site
Hi Marloes, This problem can't be solved yet. Make sure the ListFillRange is on the same sheet as your list box. 
This comment was minimized by the moderator on the site
I've just purchased kutools to use this function. Is it possible to have two or more different searchable drop down lists (i.e. referncing different lists of valid entries) on the same sheet?
This comment was minimized by the moderator on the site
Hi Marc,The feature does not support two or more different searchable drop down lists on the same sheet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
how to use this dropdown in vba form any konw please reply
This comment was minimized by the moderator on the site
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
This comment was minimized by the moderator on the site
I have around 80000 data while running excel is hang
This comment was minimized by the moderator on the site
Sir How to use this in excel userform combobox....? plz help
This comment was minimized by the moderator on the site
Hi Sourav Singha,
Can't use it in a userform combobox. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Is there a way to make it call up a hyperlink? My email is
This comment was minimized by the moderator on the site
Hi Josh,
Sorry can;t help you with that yet.
This comment was minimized by the moderator on the site
I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
This comment was minimized by the moderator on the site
Hi Vrezh,
Sorry this kind of problem can't be solved yet. Thank you for your comment.
This comment was minimized by the moderator on the site
How can I use this? I have two problem
1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
I need for I can search with typing too, so simple(not active-x) combo is wrong.

2nd how set padding? - my combo text when I search is not see whole because itt has padding.

3th if my source is C column, how drop empty elements from list
This comment was minimized by the moderator on the site
Hi Steve Olah,
Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations