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 (83 downloads)

Join Our Premium WhatsApp Groups

Because learning right is important