How to Make a Searchable Drop-Down List in Excel

A searchable drop-down list in Excel lets you type a few letters and pick from only the items that match, instead of scrolling through hundreds of names. A standard drop-down can't do this in older versions, but a short FILTER and SEARCH formula can: it lists the matching items, and data validation turns that list into the drop-down. This guide builds one step by step, and also covers the built-in search in recent Microsoft 365 versions.

All the examples use this product list in A2:A8, with a search cell in D1:

Does Excel have a searchable drop-down built in?#

In recent versions of Microsoft 365 and Excel for the web, yes: when you start typing in a cell that has a drop-down list, the list can narrow to the items that match. If you have that, try it first, because it needs no extra setup. Make a normal drop-down with Data > Data Validation > List, as shown in the guide to data validation drop-down lists, then type a few letters in the cell.

Older versions don't do this, and you can't control how the built-in search matches. If the file will be opened in older Excel, or you want matching anywhere in the text, build the formula version below.

Step 1: List the matching items with FILTER and SEARCH#

The heart of a searchable drop-down is a formula that returns only the items containing the search text. In F2, type:

=FILTER(A2:A8,ISNUMBER(SEARCH(D1,A2:A8)),"No match")

With "desk" in D1, three items spill down from F2: Desk Lamp, Standing Desk and Desk Organizer. Here is what each part does:

1. SEARCH(D1,A2:A8) looks for the search text inside every product. It returns the position where it was found, such as 1 or 10, or #VALUE! when it isn't there.
2. ISNUMBER(…) turns positions into TRUE and errors into FALSE.
3. FILTER(A2:A8,…,"No match") keeps the TRUE rows, and shows "No match" when nothing matches.

SEARCH is not case-sensitive, so "desk" finds "Desk". When D1 is empty, SEARCH finds the empty text in every item, so the whole list shows. That is exactly what you want before anyone types. The FILTER, SEARCH and ISNUMBER function guides explain each one, and the article on filtering rows that contain text shows more ways to use this pattern.

FILTER needs Excel 2021 or Microsoft 365.

Step 2: Point the drop-down at the spill range#

Now turn the results into a drop-down. Select the cell that should hold the drop-down, such as D2, then:

1. Go to Data > Data Validation.
2. Under Allow, choose List.
3. In Source, type =$F$2# and click OK.

The # after F2 means "the whole spill range starting at F2", however many rows it has. Type "lamp" in D1 and the drop-down in D2 offers only Desk Lamp and Floor Lamp. Clear D1 and it offers all seven products. The guide to dynamic drop-down lists explains spill ranges as list sources in more detail.

To keep the sheet tidy, put the helper formula on another sheet or in a hidden column. A data validation source can point to another sheet, as shown in how to use a drop-down list from another sheet.

Step 3 (optional): Search inside the drop-down cell itself#

You can use one cell for both typing and picking, with one change. Make the FILTER formula search the drop-down cell itself, such as SEARCH(D2,A2:A8), and set D2's list source to =$F$2#. Then:

1. In Data Validation, open the Error Alert tab.
2. Untick Show error alert after invalid data is entered and click OK.

Now you can type "desk" in D2 and press Enter. The list narrows, and clicking the arrow shows only the three matches. Pick one to replace your typed text. The trade-off: with the error alert off, D2 also accepts text that isn't on the list, so a separate search cell is safer for shared files.

How to sort results or match the start of the text#

Two small changes make the list nicer to use:

• Sorted results. Wrap the formula in SORT: =SORT(FILTER(A2:A8,ISNUMBER(SEARCH(D1,A2:A8)),"No match")). The matches now read Desk Lamp, Desk Organizer, Standing Desk.
• Starts with instead of contains. Replace the SEARCH test with LEFT: =FILTER(A2:A8,LEFT(A2:A8,LEN(D1))=D1,"No match"). With "desk", this returns only Desk Lamp and Desk Organizer, because Standing Desk doesn't start with desk.

To show how many items match, add a count next to the search box with a wildcard: =COUNTIF(A2:A8,"*"&D1&"*") returns 3. The * wildcards mean "any text before or after". You can practise wildcards and cell-based criteria in a live sheet.

Searchable drop-downs in older Excel#

Without FILTER, the same idea needs more work. A common approach is a helper column that marks each matching item, such as =IF(ISNUMBER(SEARCH($D$1,A2)),ROW(),""), then an INDEX and SMALL formula that lists the marked items one by one. The drop-down then points to that list. It works, but it is longer to build and maintain. If everyone has Microsoft 365, the FILTER method or the built-in search is much simpler.

For lists that depend on another choice, such as products within a chosen category, see the guide to dependent drop-down lists.

Why isn't my searchable drop-down working?#

When the list doesn't narrow, check these:

1. The source has no #. =$F$2 gives a one-item list. Use =$F$2#.
2. Something blocks the spill. If F3 or below holds data, the formula shows #SPILL! and the list breaks. Clear the cells below the formula.
3. "No match" appears as an option. That is the FILTER fallback text. Change it to "" if you'd rather show an empty list.
4. Errors when typing in the drop-down cell. Data validation is rejecting partial text. Use a separate search cell or turn off the error alert.
5. Extra spaces in the source list. "Desk Lamp " with a trailing space still matches a search, but it looks like a duplicate. Clean the list with TRIM.

Practise it#

A searchable drop-down is FILTER plus a text test. Practise the FILTER function in a live spreadsheet, then practise wildcards and cell-based criteria for partial matches. Each lesson checks your formula as soon as you type it. Then try the exercise on this page to list the products that contain "desk".