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.

• Searchable drop-down list in Excel 365
• Create searchable drop-down list (for Excel 2019 and later)
• Create searchable drop-down list easily (for all Excel versions)
• Create searchable drop-down list with Combo box and VBA (more complex)

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.

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")

   

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.

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 Searchable Drop-down List feature. This feature is available in all Excel versions and works not only on all drop-down lists within a specified range, but also on all drop-down lists in the current worksheet or the current workbook, allowing you to easily search for the desired items in drop-down lists.

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

1. Specify an option you need in the Set scope drop-down list.
   In this case, I need to make drop-down list in a specified range searchable, so I select the Specify range option and select the range.
2. Click OK.
   

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.

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.

Related articles: