This post demonstrates how to create a google like dynamic search suggestions drop down list in excel.It is very helpful if you have a large number of values in your list and scrolling down the entire list is a painful task.
Dynamic search suggestions drop down keeps filtering the values that are displayed based on each input that you provide.
Let’s see how to create a drop down in excel with dynamically changing suggestions like below.
Step 1
Create a combo box by navigating to the Developer tab->Insert->ActiveX Controls
Step 2
Right click on the combo box and go to property as shown below.
Step 3
Now Go to Alphabetic tab in the properties window and set the following properties.
Set AutoWordSelect to False
Set LinkedCell to B2
Set MatchEntry to 2 – fmMtachEntryNone from drop down as shown below and close the properties window.
Now click the Design Mode to get back to the normal Mode as shown below.
Step 4
Now insert the following formula to cell F3 and drag the formula to the end of our list.
=–ISNUMBER(IFERROR(SEARCH($B$2,E3,1),””))
Step 5
Now insert the following formula to cell G3 and drag the formula to the end of our list.
=IF(F3=1,COUNTIF($F$3:F3,1),””)
Step 6
Now insert the following formula to cell H3 and drag the formula to the end of our list.
=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),””)
Step 7
Now create a name range with the formula as shown below.
=Sheet1!$H$3:INDEX(Sheet1!$H$3:$H$22,COUNTIF(Sheet1!H3:$H$22,”?*”))
And press OK.
Step 8
Now double click the drop down in design mode and paste the below code as shown below.
ComboBox1.ListFillRange = “DropDownList”
ComboBox1.DropDown
And press the Play Button.
As you could see the drop down is working and the value could be selected by clicking.
Hope this helped.
