# Search Suggestion Drop Down In Excel

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 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.

Share The Knowledge