Function Problems

Create a Searchable List in Microsoft Excel with Formulas

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Create an Excel list that users can search with formulas, a combo box, or VBA.

Create a Searchable List in Microsoft Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the list

Enter a header in A1 and your list entries in A2:A100. Use B2 as the search cell and C2 for the drop-down.

2
Filter the entries

In E2, enter =FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(SEARCH(B2,A2:A100)),""). Leave the cells below E2 empty so the results can spill.

3
Name the results

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.

4
Create the drop-down

Select C2, open Data > Data Validation, choose List under Allow, and enter =SearchResults in the Source box. Select OK.

5
Search and select

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.

Create a filtered Data Validation list with FILTER
No matches: If no entries match the search term, the filtered result is blank. Change or clear the term in B2 to see available choices.
Search spreadsheet lists

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. 1. Open the workbook: Launch WPS Spreadsheet and select Open to choose your Excel workbook.
  2. 2. Select the list: Select the list column, including its header, and choose Data > Filter.
  3. 3. Search the filter: Select the filter arrow in the column header and enter your search term in the filter's search box.
  4. 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.
Search list entries from a column filterOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, easy-to-use office suite
microsoft office alternative - wps office

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.