Searchable Drop-Down List in Excel: Type to Search

Part of the free Module 6: Data Validation · Lesson 13 of 14 · Full Excel course

Updated on 5 September 2026 · Built-in autocomplete needs Excel 365; the FILTER method needs Excel 365 or 2021; the classic SEARCH method works in Excel 2019 and 2016 too.

A searchable drop-down list in Excel lets the user type part of an item and see only the matching entries instead of scrolling through hundreds of names. Excel 365 now does this automatically with autocomplete in every Data Validation list. In Excel 2021, 2019 and 2016 you build the same behaviour with a helper column that uses SEARCH, plus a FILTER or INDEX formula as the list source.

Table of Contents

What a searchable drop-down list is and when you need one

A standard List rule shows every item in a fixed order. With ten items that is fine; with 500 customers, products or employees it wastes time and invites wrong picks. A searchable, or filterable, drop-down narrows the list as the user types, the way a web search box does. You need one whenever the source list is long, when users know part of a name but not its position, or when the list changes often and cannot be memorised.

Method	Excel versions	Setup	Behaviour
Built-in autocomplete	Excel 365 (Windows, Mac, web)	None; any List rule	Matches text anywhere in the item as you type
FILTER and SEARCH helper	365 and 2021	One spill formula plus a List rule	List updates when the arrow is reopened
Classic INDEX and SMALL helper	2007 to 365	Helper column plus OFFSET source	Same as above, more formulas

Method 1: autocomplete in Excel 365 (no setup)

Since 2022 Excel 365 filters every Data Validation drop-down as you type. Nothing needs to be switched on.

Select the cells and go to Data > Data Tools > Data Validation. Set Allow to List and point Source at your list, for example =Lists!$A$2:$A$500.
Click OK, then select a validated cell and start typing. A shortlist of items containing the typed characters appears under the cell.
Use the arrow keys or the mouse to pick an item, then press Enter. Pressing Alt+Down still opens the full list.

The match is not limited to the start of the item: typing son shows Johnson, Sonia and Anderson. The feature works in Excel for the web and on Mac, but not in Excel 2021 or earlier perpetual versions, so a file shared with those users falls back to the ordinary list. That is why the manual methods below are still worth knowing.

Method 2: FILTER with SEARCH (Excel 365 and 2021)

This method builds a list that contains only the items matching what the user has typed into the drop-down cell itself. The source list is in Lists!A2:A500 and the drop-down cell is Sheet1!B2.

In Lists!C2 enter =FILTER(Lists!$A$2:$A$500,ISNUMBER(SEARCH(Sheet1!$B$2,Lists!$A$2:$A$500)),"No match"). When B2 is empty, SEARCH finds an empty string in every item and the full list spills.
Select Sheet1!B2 and open Data Validation. Choose List and set Source to =Lists!$C$2#.
On the Error Alert tab untick Show error alert after invalid data is entered. This lets the user type a partial word such as joh without Excel rejecting it.
Click OK. Type joh in B2, press Enter, then press Alt+Down: only the items containing joh are listed. Pick one.

Wrap the list in SORT if you want the matches alphabetised: =SORT(FILTER(...)). Because the error alert is off, add a conditional format or a helper check such as =COUNTIF(Lists!$A$2:$A$500,B2)=0 to highlight a cell where the user left a partial word instead of choosing an item.

Method 3: the classic SEARCH, SMALL and INDEX helper (all versions)

Before dynamic arrays, the same result needed three helper columns on the Lists sheet. Column A holds the items, the drop-down cell is Sheet1!B2.

Cell	Formula (fill down to row 500)	Purpose
Lists!B2	`=IF(ISNUMBER(SEARCH(Sheet1!$B$2,A2)),ROW(),"")`	Row number of every matching item, blank otherwise
Lists!C2	`=IFERROR(SMALL($B$2:$B$500,ROWS($C$2:C2)),"")`	Matching row numbers packed to the top
Lists!D2	`=IFERROR(INDEX($A:$A,C2),"")`	The matching items, packed to the top
Lists!F1	`=COUNT($B$2:$B$500)`	How many items matched

The validation source is then =OFFSET(Lists!$D$2,0,0,Lists!$F$1,1), which returns exactly the matching rows and no blanks. Turn off the error alert as in Method 2. The method is reliable but slow on very long lists, because OFFSET is volatile and the helper columns recalculate on every change.

Worked example: a 300-customer order sheet

Lists!A2:A301 holds 300 customer names. Sheet1!B2 must accept one of them. Using Method 2 in Excel 365:

Step	Action	Result
1	Lists!C2: `=SORT(FILTER(Lists!$A$2:$A$301,ISNUMBER(SEARCH(Sheet1!$B$2,Lists!$A$2:$A$301)),"No match"))`	All 300 names spill in C2:C301
2	Data Validation on B2: List, Source `=Lists!$C$2#`, error alert off	B2 shows a drop-down arrow
3	Type gupta in B2 and press Enter, then Alt+Down	Spill shrinks to Anil Gupta, Priya Gupta, Gupta Traders
4	Pick Priya Gupta	B2 holds the exact list item; the spill returns to 300 rows the next time B2 is emptied

The same sheet in Excel 365 also works with no helper at all: skip step 1, point the Source at Lists!$A$2:$A$301 and rely on autocomplete. Keep the helper version if the file is also opened in Excel 2021.

Tips and common mistakes

Turn off the error alert for Methods 2 and 3. Otherwise the partial text you type to search is rejected before the list can filter.
Use SEARCH, not FIND. SEARCH is case-insensitive, so gupta matches Gupta.
Prefix a wildcard for start-of-word matches. SEARCH("^"&B2,"^"&A2) with a marker character limits matches to items that begin with the typed text.
One helper per drop-down cell. The FILTER formula reads a specific cell, so a column of searchable drop-downs needs a helper spill per row or a VBA or ActiveX combo box instead.
Remove blanks from the source. Blank items match everything and appear at the top of every search.
Autocomplete needs an up-to-date Excel 365. If typing shows no suggestions, check File > Account > Update Options.
Do not confuse this with AutoComplete for cell values. Excel has always suggested earlier entries from the same column; the drop-down search is a separate feature tied to Data Validation.

Errors and how to fix them

Symptom	Cause	Fix
Typing a partial word gives “This value doesn’t match”	Error alert still on	Untick Show error alert on the Error Alert tab
Drop-down shows the full list, not the matches	Source points at the raw list, not the helper spill	Set Source to `=Lists!$C$2#` or the OFFSET formula
#SPILL! in the helper	Cells below the helper are not empty	Clear the column under the formula or move it
List is empty after a search	Nothing matched, or the helper returns an error	Include the if_empty argument, e.g. “No match”
#NAME? in the helper	FILTER or SORT not available in this version	Use Method 3
Search is case-sensitive	FIND used instead of SEARCH	Replace FIND with SEARCH

Practice exercise

Type 30 product names in Lists!A2:A31, including several that share a word such as Blue. Create a plain List rule and, in Excel 365, test autocomplete by typing blue.
Build Method 2 for cell B2 with the FILTER and SEARCH helper, error alert off, and confirm that blue narrows the list.
Wrap the helper in SORT and check the order changes.
Add the COUNTIF check that flags B2 when it holds text that is not an exact list item.
Rebuild the same list with Method 3 and compare how many formulas each approach needs.

Key takeaways

Excel 365 filters every Data Validation list as you type; no setup is needed.
For Excel 2021, a FILTER with ISNUMBER(SEARCH()) helper plus a spill-range Source gives the same result.
Excel 2019 and earlier use SEARCH, SMALL and INDEX helper columns with an OFFSET source.
Manual methods need the error alert switched off so partial text can be typed.
Add a COUNTIF check so a half-typed search term is not left in the cell as data.

Related lessons

Frequently asked questions

How do I make a drop-down list searchable in Excel?

In Excel 365 just create a normal Data Validation list; typing in the cell filters the list automatically. In Excel 2021 add a helper cell with =FILTER(list,ISNUMBER(SEARCH(cell,list)),”No match”), use its spill range as the list Source and switch off the error alert. Older versions use SEARCH, SMALL and INDEX helper columns with an OFFSET source.

Does Excel have autocomplete for data validation drop-down lists?

Yes. Excel 365 on Windows, Mac and the web filters Data Validation drop-downs as you type, matching text anywhere in each item. It cannot be turned off per cell and it is not available in Excel 2021, 2019 or 2016, which show the standard unfiltered list.

Why do I get an error when I type part of a word in the drop-down?

The Error Alert tab is still active, so any text that is not an exact list item is rejected. For the helper-column methods, untick Show error alert after invalid data is entered. Then protect data quality with a COUNTIF check or conditional formatting that flags cells holding text outside the list.

Can I have a searchable drop-down in every row of a table?

With the helper-formula methods each row needs its own helper spill, which is impractical beyond a few rows. Excel 365 autocomplete works in every validated cell with no helpers, so it is the practical answer for whole columns. In older versions a VBA or ActiveX combo box is the usual alternative.

Is SEARCH or FIND better for a searchable list?

Use SEARCH. It ignores case, so gupta matches Gupta, and it accepts the wildcards ? and *. FIND is case-sensitive and has no wildcards, which makes it stricter than users expect in a search box.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems with searchable lists are available at NextGenTemplates.com.