How To Create Searchable Dropdown In Excel

A Dropdown to select value is good, but a searchable dropdown in excel is the best!

In this article we will see two ways to create a searchable dropdown in excel.

  1. By using formulas
  2. By using VBA

Create Searchable Dropdown by using Formulas

Pretty easy, but only if you know a trick. We are basically going to filter the main list based on the substring we enter in a cell and will assign that filtered list in the cell validation. but all with formulas.




First step is to know the filter criteria, in the attached file you will see that we have use =Cell(“Contents”) formula. Now here is the trick if you don’t pass  any cell reference in this formula, it will pick the latest entered value from anyworksheet  in entire workbook.

When we enter a substring like ad, Cell formula will update the LastEntry cell with ad.

Here is our main list on the left and our filtered list on the right. Notice the Matching Row column. in this column, we are using our LastEntry cell value in Search formula. So, if search is found, it will return the row number of that row, else, it will return blank.

Now, have a look at FilteredList tabel’s First column, here we are using Small() function on MatchingRow column so that we can have all matching row numbers together and sorted.

Second colum of the FilteredList table is using Index formula and picking the value from City Column in left table.

One last step is to create a dynamic range on the filteredlist and assign that in our cell validation.  that formula will look like this –

=OFFSET(Data!$I$2,,,MATCH(0,Data!$I$2:$I$301,0)-1)

We can put this formula as it is in the List Validation or create a name range and put that name in the validation.

Thats it! your searchable dropdown in excel is ready to use. Refer the GIF above to see how it works.

How to create Searchable Dropdown in VBA

it is even easiser in VBA. you need couple things to do so.

  1. Userform
  2. A Public Array (Main List)
  3. A function procedure to filter array

Insert an userform in VBA and add combobox and a button on it.  in the UserForm code window, enter this code.

Private Sub UserForm_Initialize()
    ‘– Assing your list to array here.
    varArr = Intersect(Sheet1.Range(“A1”).CurrentRegion, Sheet1.Range(“A1”).CurrentRegion.Offset(1)).Columns(2)
    Me.ComboBox1.List = varArr
End Sub

Above code will execute before loading the userform. we are assigning our main list to a public array varArr

Private Sub ComboBox1_Change()

    Dim strSelected As String
    strSelected = Me.ComboBox1.Value
    If strSelected = “” Then
        Me.ComboBox1.List = varArr
    Else
        Me.ComboBox1.List = SearchAndAppend(strSelected)
    End If
    Me.ComboBox1.DropDown

End Sub

Code above is an event procedure that will execute automatically when you enter value in combobox you added on userform. notice that it is calling SearchAndAppend() function which return an array.

Function SearchAndAppend(strSubString As String)

    Dim lngR As Long
    Dim varResult

    For lngR = LBound(varArr) To UBound(varArr)
        If varArr(lngR, 1) Like “*” & strSubString & “*” Then
            If Not IsArray(varResult) Then
                ReDim varResult(0)
            Else
                ReDim Preserve varResult(UBound(varResult) + 1)
        End If
        varResult(UBound(varResult)) = varArr(lngR, 1)
        End If
    Next lngR
    If Not IsArray(varResult) Then
        ReDim varResult(0)
        varResult(0) = “No Match”
    End If
    SearchAndAppend = varResult

End Function

Basically we are saying if combbox value is blank then assign main list array on the combobox, else, Filter the main list and assign to the combobox.

Private Sub CommandButton1_Click()
    Sheet2.Range(“rngSelected”).Value = Me.ComboBox1.Value
End Sub

Above code is an event procedure that will execute automatically when  you hit the command button on userform, it will populate the cell with the selected value in combobox.

Download a working file from here




Searchable-Dropdown in Excel.zip (272 downloads)
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *