Power Query Problems

Create a Search Box for a Power Query List in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user wants to search a Power Query-generated list using an input cell rather than selecting an item from a drop-down list.

Create a Search Box for a Power Query List in Excel
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
A Power Query result contains a list of names that the user wants to filter by typing search text.
Observed behavior
Matching names should appear dynamically as the search text changes, and the underlying list should update when the query is refreshed.
Before you start

Check that your Excel version supports the FILTER function and that your Power Query result has a column containing the names to search. Reserve an input cell and an empty area outside the query table for the results.

Solution 1Recommended

Search the Power Query table with FILTER and SEARCH

Use this method to show matching names automatically as you type in a worksheet cell.

1
Load the query as a table

In Power Query Editor, select Home > Close & Load, or choose Close & Load To and select Table. Make sure the names column is headed Name.

2
Name the table

Click a cell in the loaded table, open the Table Design tab, and enter Names in the Table Name box.

3
Choose a search cell

Use cell B1 for the search text, provided it is outside the query table. If B1 is unavailable, choose another cell and change the formula reference accordingly.

4
Enter the search formula

In an empty cell outside the table, enter =FILTER(Names[Name],ISNUMBER(SEARCH(B1,Names[Name])),"No matches"). Leave enough empty cells below it for the results to spill.

5
Search and refresh

Type all or part of a name in B1 to display matches. To update the underlying list, select Data > Refresh All.

Search the Power Query table with FILTER and SEARCH
Blank searches and spill space: A blank B1 returns the full list. If Excel displays a #SPILL! error, clear any cells blocking the result area.
Free Microsoft Office alternative

Work with Excel lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use worksheet search or filters to explore list data. Power Query connections and refresh behavior may not be supported, so verify your workbook before migrating.

Search and filter worksheet lists in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLSX and XLS filesUse a familiar interface for a smoother move from ExcelWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the search distinguish between uppercase and lowercase letters?

No. The SEARCH function is case-insensitive, so typing part of a name in B1 matches either uppercase or lowercase text.

What happens if I leave the search cell blank?

With B1 blank, the formula returns the full Names[Name] list. Type a search term in B1 to narrow the results.

How do I update the list after the source data changes?

Select Data > Refresh All to refresh the Power Query result. The FILTER formula then uses the refreshed table.

Why does the formula show a #SPILL! error?

The result needs empty cells below the formula. Clear any blocking content, and place the formula outside the Power Query table.

Can I use this formula in any version of Excel?

The formula requires an Excel version that supports FILTER and dynamic arrays. If FILTER is unavailable, update Excel or use the table's column filter to search the list.