In this article you will learn how to create a searchable drop-down list in excel. Just assume you have a long list of data validation and if you have to select any item then it will take some time because you have to scroll it to find your desired item in the 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.
Below are the steps to create the a searchable drop-down list for multiple cells
Let’s say we have a country list on a worksheet (“Sheet1“) in column A.
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.
- Go to Formulas Tab and click on Define Name
- 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.
- 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–
- Now go to the “Sheet2” and select the range where you want to create searchable drop-down.
- Open the Data Validation Window (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.
- In the Paste Name window select the Country_list Name and click on OK
- Name will be reflected in the Source box.
Note: You can type the name in the source box directly like “=Country_list“
- 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.
- Searchable drop-down has been created. if you will click on drop-down arrow it will show the complete list of countries.
- If you will type some keyword and click on drop-down arrow then it will show the related list.
Click here to download this excel file