Searchable Drop Down List in Excel transforms your spreadsheets into highly interactive and user-friendly tools, enhancing data entry and retrieval efficiency. This feature is crucial for managing large datasets, improving user navigation, and ensuring data accuracy. By integrating a searchable drop-down list in Excel workbooks, you can streamline workflows, reduce data entry errors, and provide a more engaging experience for users. Master this functionality to take your Excel skills to the next level and make your data management tasks more effective and less time-consuming.
This Content Covers:
- How to Create Searchable drop down list in Excel Using VBA?
- Insert and Configure a Drop-Down box
- Setting the Data
- Inserting a Short VBA Code in VBA Module
- How to Create Searchable Drop Down List in Excel without VBA? (For Microsoft 365 only)
- Formula Input and Data Sort
- Insert Drop-Down box from Data Validation Tab
- Inserting Formula for Returning Other Information Linked to the Drop-Down Box Data
- How to Search Data from Searchable Drop-Down Box?
- Search Data from the Box Created With VBA
- Search Data from the Box Created Without VBA
1. How to create Searchable Drop Down list in Excel using VBA?
Here we have a list of country names. We will show you how you can create a searchable dropdown list for these set of names using Excel VBA.
1.1 Insert and Configure a Drop-Down box to Create a searchable Drop
Step 1: Go to Developer>>Insert. From the drop down-menu, select this icon named Combo Box (Active X Control) marked inside the picture.
Step 2: Click on anywhere in your worksheet and a drop-down box will be inserted immediately.
Step 3: Right click on your search box and click on Properties.
Step 4: From Properties dialogue box, select False from AutoWordSelect, select B3 or any cell which you want to link with your search box in Link Cell box, for MatchEntry box select MatchEntryNone, type DropDownList inside the box labeled as ListFillRange. Now close the box.
Step 5: Click on Design Mode from Developer tab. This will deactivate the edit mode and you will be able to type inside the search box.
1.2 Setting the Data to Create a Searchable Drop-Down List
Now you will have to create few helper columns and a dynamic named range to set the data from your worksheet. Follow the steps below.
Step 1: Select cell G3 and insert the formula given below. Drag the cell down o G11. This formula will return 1 if the text in the box is there and 0 if its not there.
=–ISNUMBER(IFERROR(SEARCH($B$3,E3,1),””))
Step 2: Insert the below-given formula in cell G3 and click Enter. Now drag the cell downwards to apply the formula in all those cells. When the Combo Box text matches the country name, this formula returns 1 for the first instance and 2 for the second instance and so on.
=IF(G3=1,COUNTIF($G$3:G3,1),””)
Step 3: Insert this following formula inside cell I3 and drag it down to I11. This formula stacks all the matched names together without any blank cells in between.
=IFERROR(INDEX($E$3:$E$11,MATCH(ROWS($H$3:H3),$H$3:$H$11,0)),””)
Step 4: Go to Formulas>>Name Manager and from the dialogue box select New.
Step 5: Inside the Name box type DropDownList and insert the below given formula inside the box named Refers to. As my sheet name is Data so its showing Data instead of Sheet1 in the formula. Click Ok>>Close.
=Data!$I$3:INDEX($I$3:$I$11,MAX($H$3:$H$11),1)
1.3 Inserting a Short VBA Code in VBA Module
Step 1: Right click on the worksheet name and select View Code.
Step 2: Insert the formula given below inside the VBA module and click RUN. Your searchable drop-down list has been created.
Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = “DropDownList”
Me.ComboBox1.DropDown
End Sub
2. How to Create a Searchable Dropdown List in Excel without VBA? (For Office 365 only)
Here we have a worksheet containing some data. We will show you how you can create a searchable drop-down list using this list of items without VBA.
2.1 Formula Input and Data Sort
Step 1: Select cell I1 and enter the formula given below in that cell. Press ENTER. Here we use the FILTER Function, SORT Function, SEARCH Function and ISNUMBER Function.
=SORT(FILTER(F2:F10,ISNUMBER(SEARCH(B5,F2:F10)),”not found”))
2.2 Insert Drop-Down box from Data Validation Tab
Step 1: Select cell B5 and then go to Data>>Data Validation to select the data validation list.
Step 2: Inside the Data Validation dialogue box, select List from the box named as Allow. Now copy and paste or type the formula given below inside Source box. Select Error Alert tab and uncheck the Show Alert box. Click on Apply. A drop-down check box has been inserted in cell B5.
=$I$1#
2.3 Inserting Formula for Returning Other Information Linked to the Drop-Down Box Data
Step 1: Select Cell C5 and insert the formula given below. Press Enter key to filter from a range of cells.
=FILTER(G2:G10,F2:F10=B5,””)
3. How to Search Data from Searchable Drop Down Box?
3.1 Search Data from the Box Created With VBA
Step 1: Click on the drop-down bar and some of the data will be available for you to select from the drop-down list.
Step 2: If the data or name you’re looking for isn’t there initially then start typing the data and you’re drop-down list will narrow the options for you and return you the data your’e searching.
3.2 Search Data from the Box Created Without VBA
Step 1: You may choose from the drop-down list when you click the drop-down bar to access some of the data. If the information or name you’re looking for isn’t present at first, start putting it in, and your drop-down list will restrict the available choices and give you the information you need.
Step 2: When you insert a name that is present in the initial data list, it will return you with that name’s sales value too in the next cell as shown in the picture below.
Application of a Searchable Excel Drop Down List
- User-Friendly Data Entry: Create searchable drop-down lists to streamline data entry processes, allowing users to quickly find and select items, enhancing accuracy and efficiency.
- Large Inventory Management: Use searchable drop-down lists to manage extensive inventories, enabling easy access to thousands of items without scrolling through long lists.
- Customer Information Retrieval: Implement searchable drop-down lists for customer databases, allowing staff to rapidly find and select customer names or IDs when entering or referencing orders and queries.
- Survey or Form Responses: Apply searchable drop-down lists in surveys or forms to make it easier for respondents to answer questions based on a large set of predefined options, improving response quality and user experience.
- Employee or Resource Scheduling: Utilize searchable drop-down lists to assign employees or resources in scheduling documents, simplifying the process of locating and allocating personnel or assets.
- Data Analysis Filtering: Embed searchable drop-down lists in data analysis sheets to quickly filter datasets based on key attributes, facilitating faster and more dynamic data exploration.
You may be interested:
