Function Problems

Create a Searchable Drop-Down List in Microsoft Excel

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

Create an Excel drop-down list that makes it easier to find an option by typing.

Create a Searchable Drop-Down List in Microsoft Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to search a list of options instead of scrolling through a standard Data Validation drop-down.
Observed behavior
A matching option can be found by typing, either in a supported drop-down or in a separate search cell.
Before you start

Check your Excel version and place your source options in a single column. The FILTER method requires a version that supports dynamic arrays and a clear area for the filtered results to spill.

Solution 1Recommended

Filter a List with a Search Cell in Excel

Use a separate search cell and a dynamic helper list to narrow down Data Validation options in supported Microsoft 365 versions.

This method searches from a separate cell, not inside the drop-down itself. The list updates as you type in the search cell.

1
Set up the source list

Enter your options in cells A2:A100, leaving unused cells blank. Use D2 as the search cell and F2 as the cell that will contain the drop-down.

2
Add the filter formula

Enter =FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(SEARCH(D2,A2:A100)),"No matches") in E2. Keep the cells below E2 clear so the results can spill.

3
Create the drop-down

Select F2, then choose Data > Data Validation. Under Allow, select List, enter =$E$2# in Source, and click OK.

4
Search and select

Type part of an option in D2, then open the drop-down in F2 to select a matching result. Clear D2 to show all nonblank options.

Filter a List with a Search Cell in Excel
Check spill reference support: If Excel does not accept =$E$2# as the Data Validation source, try creating a named range in Formulas > Name Manager that refers to the spill range, then use that name as the source. If there are no matches, the list displays “No matches.”
Free Microsoft Office alternative

Manage Spreadsheet Lists with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move existing workbooks. It does not change how drop-downs behave in Microsoft Excel.

Create and edit spreadsheet lists in WPS SpreadsheetsOpen and save Microsoft Excel XLSX filesWork in a familiar, easy-to-use interfaceMove existing workbooks without starting from scratch
microsoft office alternative - wps office

Frequently Asked Questions

Can I search by typing directly in an Excel drop-down?

Some Microsoft 365 versions support autocomplete in Data Validation cells. Select the validated cell and start typing to check whether matching entries appear.

Does the FILTER method search anywhere in an option?

Yes. The SEARCH function in the formula matches text anywhere in each option and is not case-sensitive. Type the search text in D2 to update the helper list.

Why does my filtered list show a spill error?

The cells below E2 may contain data. Clear the area where the FILTER results need to appear, then check the formula in E2 again.

What can I use if my Excel version does not support FILTER?

Consider a ComboBox or VBA UserForm for a searchable interface. Store the source options in an Excel Table so the list is easier to maintain.