In this article you will learn how to create a Searchable Excel Drop down list in excel. Just assume you have a lengthy list in excel data validation and if you must select any item then it will take some time because you must scroll it to find your desired item in the excel data validation list.
Here I have created a solution of this and that is Searchable Drop-Down List. Just type the any keyword on an excel cell and click on drop-down arrow, you will see the drop-down list related your keyword.
Searchable Excel Drop down list
![Searchable Excel Drop down list for Multiple Cells](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown.jpg)
Below are the steps to create drop down lists and make then searchable for multiple cells
Let’s say we have a country list on a worksheet (“Sheet1“) in column A.
![Country list](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-1.jpg)
Put the below given formula on column B,C and D
- Put the headers – “Search” on cell “B1“, “Frequency” on cell “C1” and “Final List” on cell “D1“
- Put the formula “=IFERROR(SEARCH(INDIRECT(CELL(“address”)),A2),0)“on cell “B2“.
- Put the formula “=IF(B2=0,””,COUNTIF($B$2:B2,”>0″))”on cell “C2“.
- Put the formula “=IFERROR(INDEX(A:A,MATCH(ROW(B1),C:C,0)),””)“on cell “D2“.
- Fill the formulas till the end of list.
![Support columns](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-2.jpg)
- Go to Formulas Tab and click on Define Name
![Define Name option](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-3.jpg)
- New Name window will be opened.
- Give the Name as Country_Name.
- Put the formula “=OFFSET(Sheet1!$D$2,,,COUNTIF(Sheet1!$D:$D,”*?”)-1)” in “Refer to” box.
![New Name Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-4.jpg)
- We will create our searchable drop-down on “Sheet2“.
- Go to Visual Basic Editor (Press Alt+F11)
- Double click on “Sheet2“
- Paste the below given code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
Below is the snap set of Visual Basic Editor–
![Visual Basic Editor](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-4.1.jpg)
- Now go to the “Sheet2” and select the range of cells where you want to create searchable drop-down.
![Selection on sheet2 where we will create searchable drop-down](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-5.jpg)
- Open the data validation dialog box (use shortcut key – Alt+D+L)
- Select the list in allow drop-down.
- Click in “Source” box and press F3 to open the Paste Name window.
![Data Validation Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-6.jpg)
- In the Paste Name window select the Country_list Name and click on OK
![Paste Name window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-7.jpg)
- Name will be reflected in the Source box.
Note: You can type the name in the source box directly like “=Country_list“
![Data Validation Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-8.jpg)
- Now go to the Error Alert tab of data validation window.
- Uncheck the “Show error alert after invalid data is entered” check box.
- Click on OK button.
![Error Alert tab of data validation window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-9.jpg)
- Searchable drop-down has been created. if you click on drop-down arrow, it will show the complete list of countries.
![Countries list in drop-down](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-10.jpg)
- If you will type some keyword and click on drop-down arrow, then it will show the related list.
![Searchable Drop-down](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ExcelTips/SearchableDownDown/Searchable-Dropdown-11.jpg)
Click here to download this excel file
Watch the video tutorial:
Visit our YouTube channel to learn step-by-step video tutorials