Create a Searchable List in Microsoft Excel with Formulas
Question details
Create an Excel list that users can search with formulas, a combo box, or VBA.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to find and select an item from a list without scrolling through every entry.
- Observed behavior
- The desired list narrows choices based on a search term or provides a searchable control.
Place the list entries in one column with a header, and leave space for a search cell and filtered results. Check whether your Excel version supports FILTER and dynamic arrays; older versions require a different approach.
Create a filtered Data Validation list with FILTER
Use this formula-based approach in Microsoft 365 or another Excel version that supports FILTER and dynamic arrays.
This method uses a separate search cell to narrow the choices in a drop-down. It does not require typing directly into the drop-down.
Enter a header in A1 and your list entries in A2:A100. Use B2 as the search cell and C2 for the drop-down.
In E2, enter =FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(SEARCH(B2,A2:A100)),""). Leave the cells below E2 empty so the results can spill.
Open Formulas > Name Manager, select New, and name the range SearchResults. Set Refers to to =Sheet1!$E$2#, replacing Sheet1 with your actual sheet name.
Select C2, open Data > Data Validation, choose List under Allow, and enter =SearchResults in the Source box. Select OK.
Type part of an entry in B2, then open the drop-down in C2 to choose from the matching results. Clear B2 to show all nonblank entries.

Use a VBA-enabled searchable list in desktop Excel
Use a custom search form or an existing VBA-enabled workbook when FILTER is unavailable or you need a more interactive control.
Search an Excel list with WPS Spreadsheet filters
Open your workbook in WPS Spreadsheet and use a column filter to find matching entries. This filters the visible rows rather than creating a searchable drop-down.
- 1. Open the workbook: Launch WPS Spreadsheet and select Open to choose your Excel workbook.
- 2. Select the list: Select the list column, including its header, and choose Data > Filter.
- 3. Search the filter: Select the filter arrow in the column header and enter your search term in the filter's search box.
- 4. Show matching entries: Apply the filter to display the matching rows. Clear the filter from the column menu to show the full list again.

Frequently Asked Questions
Can I search directly inside the Data Validation drop-down?
The formula method uses a separate search cell to filter the drop-down choices. Direct typing behavior varies by Excel version; a custom VBA form can provide a different search interface.
Why does the FILTER formula show a spill error?
Check the cells below the formula for existing content or merged cells, and clear the spill area. Also make sure the formula is not inside an Excel table, where spilled arrays are not supported.
What can I use if my Excel version does not have FILTER?
You can use a custom combo box or VBA search form in desktop Excel. An existing VBA-enabled workbook must contain the necessary code and controls.
Why should I save a VBA workbook as XLSM?
The XLSM format preserves VBA code. Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm), and enable macros only when you trust the workbook's source.




