How to create a searchable drop-down in Excel in 5 minutes ~ The Independent Learning

Hi friends, Welcome to The Independent learning, I am here with another great topic.

How to insert drop down list in excel

A Facebook friend asked me to create the Searchable drop-down menu in excel. So i have tried and hoped that this will help him. Create a searchable drop down list in Excel just like Google. This trick is fantastic for large lists. Giving the functionality to search within a list goes beyond a standard drop down list. This Article tutorial will show you how to create a combo box control on a spreadsheet and then use formulas to create a dynamic searchable drop down list. This list would look awesome on your Excel dashboards and reports.

The article covers multiple Excel formulas and functions including INDEX, COUNTIFS, and ROWS. It then uses a simple line of VBA code for the drop down combo box

In this article, I will show you how to create a Searchable drop-down menu in excel. This Drop down will work like google search engine and you will be able to fetch data within a second from your resource directory.

How to create a searchable drop-down in Excel in 5 minutes, Data validation in excel, Advance data validation, Google search in excel, How to insert drop down list in excel, How to create a searchable drop down list in Excel?For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values..., Learn how to create an Excel drop down that shows suggestions as you type. A step-by-step tutorial to create an Excel Drop Down List with Search Suggestions

I assume that your excel is compatible with macro and you have enabled the Developer Toolbar, and saved your file as .xlsm format. If you don't get this done, please do it first.

Setting Up things:

In Excel 2010 and 2013

Step 1- Click File Menu.
Step 2- Click Options.
Step 3- And in the Options dialog box, Click Customize Ribbon.
Step 4- Check the Developer box, then click the OK button.

In Excel 2007,

Step 1- Click Office button (Top most left, looks like windows logo)
Step 2- Click Excel Options.
Step 3- And in the Excel Options dialog box, Click Popular Tab.
Step 4- Check the Show Developer tab in the Ribbon box, then click the OK button.

Now you are all set for the preparation on Searchable Drop down list.

Watch the full practical video

Please follow the below mentioned steps carefully:

Step 1. Click Developer bar >> Click Insert >> In ActiveX Controls sector select Combo Box to insert the same. See below:

Step 2. Draw the Combo box by Clicking and Dragging. Right Click the Combo box and then click on Properties.
Step 3. In Properties Dialog box please make following changes.
(a) Select False in the AutoWordSelect field.
(b) Specify a cell in the LinkedCell field. In this case, we enter G2.
(c) Select 2-fmMatchEntryNone in the MatchEntry field.
(d) Type DropDownList into the ListFillRange field then close.
See Below:

Step 4. Paste all your data in Column A.
Step 5. Paste this formula in the cell C2 =--ISNUMBER(IFERROR(SEARCH($g$2,A2,1),"")) and drag till last data cell In Column A. See Below:

Step 6. In Cell D2 Put this formula =IF(C2=1,COUNTIF($C$2:C2,1),"") and drag till last data cell In Column A. See below:

Step 7. In Cell E2 paste this formula =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"") and drag till last data cell In Column A. See Below:

Step 8. Click On Formulas bar >> Click on Define Name >> Type "DropDownList" in the Name field.
Step 9. Put this =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1) formula in Refers to box. Then Click Ok. See Below:

Step 10. Dubble click the Combo Box that you inserted. VBA editor window will appear. Copy and paste the below mentioned code in the VBA editor.

Private Sub ComboBox1_GotFocus()

'Made by Vikash kumar

'The Independent Learning

'independentjournals.blogspot.com

'independentlearning.in

    ComboBox1.ListFillRange = "DropDownList"

    Me.ComboBox1.DropDown

End Sub

How to create a searchable drop-down in Excel in 5 minutes

Step 11. Close the VBA editor, And deselect the Design mode. And your Searchable drop down list in excel is ready.

Please Download the practice file from here
Must watch the practice video. this will help you to understand the topic.

Most commonly used Excel formulas.

How to use Vlookup in Excel.
How to import data from the web into Excel.
How To Make Shared Excel File And Allow To Edit By Multiple Users at one time.

Thanks For reading, I hope you got your problem. Please like and share this article on facebook if you found this useful. You can also comment your queries for fast response. Stay tuned for further videos
Feel free to reach us. Info.indelearning@gmail.com

Thanks in Advanced.
Happy Learning

The Independent Learning