Searchable drop down list excel without vba

Excel now supports searchable drop-down lists!

Until this new feature hit the streets, searchable drop-down lists required writing complex VBA code coupled with advanced Excel features to work properly.

Searchable drop-down lists now work without any user effort whatsoever.  The feature is native to Excel.

In January 2022, this feature is available to select Beta Channel users of Office 365.  When officially released, the feature will be rolled out to Office 365 and standalone Excel users running the latest yearly version

Let’s look at an example of this wonderful, soon-to-be-released, new feature.

“Does Excel have a feature to allow a Data Validation list to be searchable?”

This is a common question asked on this site and most any Excel training venue.

What do we mean by a searchable Data Validation list?  Let’s look at the example below.

A traditional dropdown list provided via the Data Validation tool displays a list of items from a predefined list.  The issues with the list are multifold:

• The list is not sorted; i.e. if new items are added to the list, the list does not automatically re-sort.
• The list is not searchable. Locating a specific item in an unsorted list can be both tedious and time consuming, especially when the list contains hundreds or thousands of items.
• The list does not automatically suppress duplicate items. If an item exists multiple times in the source list, the dropdown list will display all items regardless of repetition.

Although all the above-mentioned issues are worth investigating, this post’s focus is on the searchable aspect of the Data Validation list.

Comments are closed.

We all use Google as a part of our daily routine. One of its features is search suggestion, where Google acts smart and gives us a list of suggestions while we are typing.

In this tutorial, you’ll learn how to create a searchable drop-down list in Excel – i.e., a drop-down list that will show the matching items as you type.

Below is a video of this tutorial (in case you prefer watching a video over reading the text).

Searchable Drop Down list in Excel

For the purpose of this tutorial, I am using the data of Top 20 countries by GDP.

The intent is to create an excel drop down list with a search suggestion mechanism, such that it shows a drop down with the matching options as I type in the search bar.

Something as shown below:

To follow along, download the example file from here

Creating the searchable drop-down list in Excel would be a three-part process:

1. Configuring the search box.
2. Setting the Data.
3. Writing a short VBA Code to make it work.

Step 1 – Configuring the Search Box

In this first step, I will use a combo-box and configure it so that when you type in it, the text is also reflected in a cell in real time.

Here are the steps to do this:

1. Go to Developer Tab –> Insert –> ActiveX Controls –> Combo Box (ActiveX Control).
   • There is a possibility you may not find the developer tab in the ribbon. By default, it is hidden and needs to be enabled. Click here to know how to get the developer tab in the ribbon in Excel.
     
2. Move your cursor to the worksheet area and click anywhere. It will insert a combo box.
3. Right-click on the Combo Box and select Properties.
   
4. In the properties dialogue box, make the following changes:
   • AutoWordSelect: False
   • LinkedCell: B3
   • ListFillRange: DropDownList (we will create a named range with this name in step 2)
   • MatchEntry: 2 – fmMatchEntryNone

(Cell B3 is linked to the Combo Box, which means that anything you type in the Combo Box is entered in B3)

1. Go to Developer tab and click on Design Mode. This will enable you to enter text in the Combo Box. Also, since cell B3 is linked to the combo box, any text that you enter in the combo box would also be reflected in B3 in real-time.

Step 2 – Setting the Data

Now that the search box is all set, we need to get the data in place. The idea is that as soon as you type anything in the search box, it shows only those items that have that text in it.

To do this, we will use

• Three helper columns.
• One dynamic named range.

Helper Column 1

Put the following formula in cell F3 and drag it for the entire column (F3:F22)

=--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))

This formula returns 1 when the text in the Combo Box is there in the name of the country on the left. For example, if you type UNI, then only the values for United States and United Kingdom are 1 and all the remaining values are 0.

Helper Column 2

Put the following formula in Cell G3 and drag it for the entire column (G3:G22)

=IF(F3=1,COUNTIF($F$3:F3,1),"")

This formula returns 1 for the first occurrence where Combo Box text matches the country name, 2 for the second occurrence, 3 for the third and so on. For example, if you type UNI, G3 cell will display 1 as it matches United States, and G9 will display 2 as it matches United Kingdom. The rest of the cells will be blank.

Helper Column 3

Put the following formula in cell H3 and drag it for the entire column (H3:H22)

=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")

This formula stacks all the matching names together without any blank cells in between them. For example, if you type UNI, this column would show 2 and 9 together, and rest all cell would be blank.

Creating the Dynamic Named Range

Now that the helper columns are in place, we need to create the dynamic named range. This named range will only refer to those values that match the text entered in the combo box. We will use this dynamic named range to show the values in the drop-down box.

Note: In step 1 we entered DropDownList in the ListFillRange option. Now we will create the named range with the same name.

Here are the steps to create it:

1. Go to Formulas –> Name Manager.
2. In the name-manager dialogue box click New. It will open a New Name dialogue box.
3. In the Name Field enter DropDownList
4. In the Refers to Field enter the formula:  =$H$3:INDEX($H$3:$H$22,MAX($G$3:$G$22),1)

Step 3 – Putting the VBA Code to Work

We are almost there.

The final part is to write a short VBA code. This code makes the drop down dynamic such that it shows the matching items/names as you are typing in the search box.

To add this code to your workbook:

1. Right-click on the Worksheet tab and select View Code.
2. In the VBA window, Copy and Paste the following code: Private Sub ComboBox1_Change() ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown End Sub

That’s it!!

You are all set with your own Google type Search bar that shows matching items as you type in it.

For a better look and feel, you can cover cell B3 with the Combo Box and hide all the helper columns. You can now show off a little with this amazing Excel trick.

To follow along, download the file from here

What do you think? Would you be able to use this search suggestion drop-down list in your work? Let me know your thoughts by leaving a comment.

If you have enjoyed this tutorial, I am sure you would like the following Excel tutorials too: