Computer >> Computer tutorials >  >> Software >> Office

Create a Searchable Drop Down List in Excel (2 Methods)

This article illustrates how to create a searchable drop-down list in excel. It shows the 2 ways you can do that in excel using formulas and VBA. The following picture highlights the purpose of this article. Have a quick look through the article to see how it’s done.

Create a Searchable Drop Down List in Excel (2 Methods)

You can download the practice workbook from the download button below.

2 Ways to Create a Searchable Drop Down List in Excel

Imagine you have the following dataset in the worksheet names as States. The dataset contains information about the first 13 states of the USA.

Create a Searchable Drop Down List in Excel (2 Methods)

Now you want to create a searchable dropdown list in cell B4 in the worksheet named Dropdown.

Create a Searchable Drop Down List in Excel (2 Methods)

Then you need to follow the steps highlighted in the following methods.

1. Create a Searchable Drop Down List with Formulas in Excel

First, we will create a searchable dropdown list using excel formulas. Follow the steps below to be able to do that.

📌 Steps

  • First, enter the following formula in cell E5 in the sheet named States.
=FILTER(B5:B17,ISNUMBER(SEARCH(Dropdown!B4,B5:B17)),"Not Found")
  • The SEARCH function in the formula searches for a given value.
  • The ISNUMBER function returns True if the output of the Search function is a number. Otherwise, it returns False.
  • The FILTER function filters data according to the given criteria.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Then select cell B4 in the Dropdown worksheet. Next select Data >> Data Validation.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Then select the Settings tab in the Data Validation window. Next choose List in the Allow: field using the dropdown arrow.
  • Then enter the following formula in the Source field.
=States!$E$5#
  • After that, go to the Error Alert tab.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Now uncheck Show error alert after invalid data is entered. Then hit the OK button.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Finally, a searchable dropdown list has been created. Now type something (new) in cell B4. Then select the dropdown arrow visible at the lower right corner of the cell. After that, you will see all the relevant search results as shown in the following picture.

Create a Searchable Drop Down List in Excel (2 Methods)

Read More: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)

Similar Readings:

  • How to Copy Filter Drop-Down List in Excel (5 Ways)
  • Create Excel Drop Down List from Table (5 Examples)
  • How to Create List from Range in Excel (3 Methods)
  • Auto Update Drop Down List in Excel (3 Ways)
  • How to Create Multi Select Listbox in Excel

2. Create a Searchable Drop Down List with Excel VBA

Now, suppose you don’t want to select any dropdown arrow to see the relevant results. Rather you want to see the search results as shown in Google Search. Then follow the steps below.

📌 Steps

  • First, you need to follow the steps before Data >> Data Validation only in the earlier method.
  • Then select cell E5 in the States worksheet. After that, select Formulas >> Name Manager.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Next select New in the Name Manager window to define a list.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Then change the Name to Dropdown_List in the New Name window.
  • After that enter the following formula in the Refers to field. Then hit the OK button. The formula uses the INDEX and COUNTIF functions.
=States!$E$5:$E$5:INDEX(States!$E$5:$E$17,COUNTIF(States!$E$5:$E$17,"?*"))

Create a Searchable Drop Down List in Excel (2 Methods)

  • Now go to the Dropdown worksheet. Then select Insert >> Combo Box from the Developer tab.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Next drag the mouse to properly resize the ComboBox as shown below.

Create a Searchable Drop Down List in Excel (2 Methods)

  • After that, you will see a new ComboBox created as follows.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Now right-click on the ComboBox and select Properties.

Create a Searchable Drop Down List in Excel (2 Methods)

  • After that, select the Alphabetic tab in the Properties window. Then make the following changes: AutoWordSelect >> False, Linked Cell >> B4, MatchEntry >> 2 – fnMatchEntryNone.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Now copy the following code.
Private Sub ComboBox1_Change()

ComboBox1.ListFillRange = "Dropdown_List"

Me.ComboBox1.DropDown

End Sub
  • After that, double-click on the ComboBox. This will take you directly to a new module in the Microsoft VBA window. Then paste the copied code in the blank module as shown below. Next press F5 to run the code.

Create a Searchable Drop Down List in Excel (2 Methods)

  • Finally, the searchable dropdown will work like Google Search.

Create a Searchable Drop Down List in Excel (2 Methods)

Read More: Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)

Things to Remember

  • You need to deselect the Design Mode in the Developer tab to be able to type in the ComboBox.
  • Don’t forget to make sure that the absolute references are entered properly in the formulas.
  • Use CTRL+SHIFT+Enter in case the array formulas are not working.

Conclusion

Now you know how to create a searchable drop-down list in excel. Please use the comment section below for further queries or suggestions and also let us know if you have been able to do that yourself. You can also visit our ExcelDemy blog to read more on excel. Stay with us and keep learning.

Related Articles

  • How to Create a Form with Drop Down List in Excel
  • Hide or Unhide Columns Based on Drop Down List Selection in Excel
  • How to Select from Drop Down and Pull Data from Different Sheet in Excel
  • How to Remove Used Items from Drop Down List in Excel (2 Methods)
  • Remove Duplicates from Drop Down List in Excel (4 Methods)