Searchable Dropdown
Excel Tips and Tricks

Searchable Drop down list for Multiple Cells

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.

Searchable drop-down list
Searchable drop-down list

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.

Country list
Country list

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
Support columns
  • Go to Formulas Tab and click on Define Name
Define Name option
Define Name option
  • 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
New Name Window
  • 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
Visual Basic Editor
  • Now go to the “Sheet2” and select the range where you want to create searchable drop-down.
Selection on sheet2 where we will create searchable drop-down
Selection on sheet2 where we will 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.
Data Validation Window
Data Validation Window
  • In the Paste Name window select the Country_list Name and click on OK
Paste Name window
Paste Name window
  • 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
Settings tab of Data Validation Window
  • 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
Error Alert tab of data validation window
  • Searchable drop-down has been created. if you will click on drop-down arrow it will show the complete list of countries.

 

Countries list in drop-down
Countries list in drop-down
  • If you will type some keyword and click on drop-down arrow then it will show the related list.
Searchable Drop-down
Searchable Drop-down

 

Click here to download this excel file

 

Watch the video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *