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.