Free Excel workbook
Get the Searchable Drop-Down Workbook
Download the ready-to-use workbook and practice the exact setup from the tutorial.
- Practice building a searchable drop-down list in Excel
- See the VBA setup already built so you can test it right away
- Follow the tutorial without building the workbook structure from scratch
Enter your email below for instant access.
Free. Instant access.
How Does Native Searchable Autocomplete Work in Microsoft 365?
If you have Microsoft 365 (Windows, Mac, or mobile), every Data Validation drop-down list is already searchable. There is nothing to enable or configure.
How to use it:
- Create a Data Validation list as you normally would (Data > Data Validation > List).
- Click in the cell with the dropdown.
- Start typing. Excel filters the list in real time, showing only items that contain the characters you typed. The match works anywhere in the text, not just the beginning.
- Select the matching item from the filtered list or press Enter.
The native autocomplete also suppresses blank cells automatically. If your source range has empty cells at the end (for future entries), they will not appear in the dropdown.
Limitations of the native autocomplete:
- It does not sort the list. Items appear in their original order.
- It does not remove duplicates. If the source has repeated values, they all appear.
- It is not available in Excel 2019 or Excel 2021 (one-time purchase versions).
- You cannot customize the matching behavior (e.g., match only from the beginning of the text).
If any of these limitations apply to you, use the FILTER method below instead. It gives you full control over sorting, deduplication, and matching logic.
For a full walkthrough of creating standard and searchable drop-down lists, see our main guide: How to Create a Drop-Down List in Excel.
What Does a Custom Searchable Drop-Down Look Like?
The solution we will build allows us to type in a collection of letters, click a dropdown arrow, and display a list of items that contain those letters anywhere in the item.
If we wish to see the full list, we erase any and all letters from the search cell and click the dropdown arrow. With no items to search for, the complete list is displayed.
Performing a quick Internet search for “searchable drop down lists in Excel” will return links taking you to websites demonstrating solutions using VBA. I have a similar solution using VBA that is covered in my Excel VBA & Macros course using User Forms.
“I don’t want to use VBA to solve this dilemma”, you say?
Let’s look a solution to the searchable drop down list in Excel using a rather new feature; Dynamic Arrays.
💡If you’re subscribed to Microsoft 365, good news! An even simpler solution has been available since 2023. Discover more in our article 👉 HERE.
How to Build a Searchable Drop-Down with FILTER and SEARCH
Until recently, performing many advanced Excel operations required writing complex and lengthy formulas to feed elaborate data preparation tables. This was most likely the exclusive domain of the ultra-advanced Excel user.
Not any more! We will solve this problem using Dynamic Arrays.
❗ Dynamic Arrays are only available in Office 2021+ and to Office 365 subscribers.
We wish to have the searchable dropdown list on the sheet named “Report” in cell B5 in the sample file.
When the user searches for and selects a name from cell B5, we want the selected name’s company to appear in cell C5.
We have another sheet named “MasterData” that contains a list of all the customers and their associated companies.
This list is not an official Data Table, but later we will “upgrade” this plain table to an official Excel Data Table to leverage the ability to add new items to the table and have the rest of our solution update automatically.
Although we have the full list of customers/companies, we need to derive from that a filtered version that only contains items that match the search criteria supplied in cell B5.
If we type in the letters “GAR” in cell B5, we need a list generated that contains any and all items from the Customer column that contain the letters “GAR”.
Why You Need a Data Preparation Column
Dynamic Arrays work well with named ranges stored in the Name Manager. The issue is that Data Validation dropdown lists do not work with named ranges that contain Dynamic Array references.
Because of this restriction, we need to create a data preparation table that will cull the information from the Customer column and place it in column D of the “MasterData” sheet.
Step 1: Use SEARCH to Find Matching Text
NOTE: To simplify the demonstration, we will pretend that the Data Validation search list is in cell D1 of the “MasterData” sheet. This will keep us from having to switch back-and-forth between sheets during the development of the solution.
When looking for all items that contain the letters “GAR”, enter the letters “GAR” in cell D1.
In cell D2, we will create a function to perform the search.
The SEARCH function has three parameters:
- “Find_Text” – this is the text or cell holding the text to be located
- “Within_Text” – this is the cell or array of cells to be searched
- “[Start_Num]” – this is an optional parameter that allows you to define the character position within the “Within_Text” parameter you want to start searching. If this parameter is undeclared, the search begins at character position 1 (far left).
Our first test will be to locate the text in cell D1 within the text located in cell A2.
=SEARCH(D1,A2)
We are presented with the result “1”.
This is because the letters “GAR” appears starting in the first character position counting from the left side of the data.
If we change the search criteria to the letter “M”, we are returned the number “6”, since the “M” occurs in the sixth character position of the name “Gary Miller”.
If we change the search criteria to something that does not appear in the search text, such as the letter “V”, we are presented with a #VALUE! error.
Let’s update our formula to include the full range of Customer names.
=SEARCH(D1, A2:A32)
The result is quite the attention getter!
This is the power of Dynamic Arrays.
Dynamic Arrays allow us to write a single formula but return many results. In this case, we wish to locate the text in cell D1 within every item of the Customer list. The answers appear in cells D2:D32.
This is known as a “spilled array”.
If we change the search criteria to “ROB”, we see that the letters appear in four of the Customer names, yielding numbers, and #VALUE! errors for all other list items.
Step 2: Convert Results to TRUE/FALSE with ISNUMBER
If the response to a search returns a number, we want to display the word “True”, and where there are errors, we want to display the word “False”.
Update the SEARCH function in cell D2 to read as follows:
=ISNUMBER(SEARCH(D1, A2:A32))
We now have a list of True/False responses.
Step 3: Filter the List with the FILTER Function
The FILTER function is a new Dynamic Array function that has the following parameters:
- “ARRAY” – The list of items to filter.
- “Include” – The item to be located within in the array.
- “[If_Empty]” – An optional parameter of what to display if no items are returned from the filter, such as empty text or a message.
We need to update our formula in cell D2 to filter the Customer column based on the examination previously performed by the ISNUMBER/SEARCH formula. Since the formula is returning True/False response, any item with a True response will be included in the resultant list, while any item with a False response will be filtered from the list.
Update the formula as follows:
=FILTER(A2:A32, ISNUMBER(SEARCH(D1, A2:A32)), ”Not Found”)
Observe the result of the updated formula.
If we test this formula by entering different search criteria in cell D1, we are presented with different results.
If we search for something that is not in the list of Customers, like “Leila”, the formula returns the message “Not Found”.
Step 4: Connect the Formula to the Report Sheet
Cell D1 on the “MasterData” sheet was our testing site for text to be searched. Our official search location is on the “Report” sheet.
Let’s erase what we have in cell D2 on the “MasterData” sheet and place a title in its place, like “Data Validation Prep.”
We now need to update the reference in the SEARCH function to look for the data entered in cell B5 of the “Report” sheet. Update the formula as follows.
=FILTER(A2:A32, ISNUMBER(SEARCH(Report!B5, A2:A32)), ”Not Found”)
If we place the text “GAR” in cell B5 of the “Report” sheet, the data preparation table in column D of the “MasterData” sheet updates accordingly.
How to Add UNIQUE and SORT to the Searchable Dropdown
If the list of Customers in column A of the “MasterData” sheet contained duplicate items, another function that could be incorporated into our logic is the UNIQUE function. Observe the following formula.
=UNIQUE(FILTER(A2:A32, ISNUMBER(SEARCH(Report!B5, A2:A32)), ”Not Found”))
This will produce a list of items without duplicates.
Another feature you may wish to incorporate is to have the list sorted, even when new items are added to the bottom of the source list. This is accomplished with the new SORT function. Observe the following formula.
=SORT(FILTER(A2:A32, ISNUMBER(SEARCH(Report!B5, A2:A32)), ”Not Found”))
Step 5: Create the Data Validation List from the Spilled Array
To create the Data Validation dropdown list, select Data (tab) -> Data Tools (group) -> Data Validation.
On the Settings tab in the Data Validation dialog box, select “List” from the Allow dropdown.
In the Source field, enter select the first cell in the data preparation table on the “MasterData” sheet. Because we want to capture the entire spilled array that begins in cell D2, add a “#” pound (or hashtag) symbol to the end of the reference.
=MasterData!$D$2#
Step 6: Suppress the Error Alert for Custom Text Entry
Currently, the Data Validation rules do not allow for the entering of information not previously defined in the source list.
If we enter something like “ROB” in cell B5, we are presented with the following error message.
To allow for the entering in of previously undefined data, and to suppress this error message, select Data (tab) -> Data Tools (group) -> Data Validation.
On the “Error Alert” tab, uncheck the option for “Show error alert after invalid data is entered”.
Now, if we enter the letters “ROB” in cell B5 and click the dropdown button, we are presented with a list of items containing the letters “ROB”.
How to Return Related Data When a Name Is Selected
As stated early on in this post, the objecting was to select a customer name from a dropdown list and return the customer’s company name.
We can achieve this with tried and true Excel functions like VLOOKUP or INDEX/MATCH, but since we’re using the new Excel Dynamic Array function, let’s capitalize once again on the FILTER function.
On the “Report” sheet, click in cell C5 and enter the following formula.
=FILTER(MasterData!B2:B32, MasterData!A2:A32-Report!B5, ””)
In English, this reads as follows:
Return an item from the Company column (column B) where the name in the Customer column (column A) matches the name placed in cell B5 of the “Report” sheet. If the name is not found, return empty text (two double quotes).
How to Make the Dropdown Expand Automatically with an Excel Table
To leverage the automatic inclusion of new information in our Customers table, we need to convert our traditional plain table into an official Excel Data Table.
Select any cell in the Customer/Company table and press CTRL + T (or Home (tab) -> Styles (group) -> Format as Table -> select a style) and click OK.
We will rename our table by selecting Table Tools – Design (tab) -> Properties (group) -> and enter the name “TableCustomers” in the Table Name field. This step is optional, but considered a best practice.
Because everything is already set with Dynamic Arrays, all we must do is add a new item to the Customer/Company list and test for that new item.
Our formulas automatically update to include the expanded range when we place new items in the Data Table list.
Native Autocomplete vs FILTER Method: Which Should You Use?
| Feature | Native Autocomplete (M365) | FILTER + SEARCH Method |
|---|---|---|
| Setup required | None (built-in) | Formula in helper column + Data Validation |
| Excel version | Microsoft 365 only | Microsoft 365 or Excel 2021 (needs Dynamic Arrays) |
| Sorts results | No | Yes (wrap in SORT) |
| Removes duplicates | No | Yes (wrap in UNIQUE) |
| Custom match logic | No (always contains-match) | Yes (starts-with, ends-with, case-sensitive) |
| Returns related data | No (need a separate XLOOKUP/FILTER) | Yes (build into the same formula setup) |
| Works across multiple rows | Yes (copy the cell) | Requires one helper column per row (complex) |
| Maintenance | Zero | Formula must be updated if structure changes |
Bottom line: Use native autocomplete if you have Microsoft 365 and just need basic searchable filtering. Use the FILTER method if you need sorted, deduplicated, or custom-filtered results, or if you are on Excel 2021 without the native feature.
How to Repeat the Searchable Dropdown for Every Row
The FILTER method above creates a searchable dropdown for a single cell. If you need the same searchable dropdown repeated down an entire column (for example, rows 5 through 100), the setup requires a separate helper column formula for each row.
The key changes are:
- Each row’s FILTER formula must reference its own cell (B5, B6, B7…) instead of a fixed cell.
- The Data Validation source for each row must point to its own helper column output, using a relative reference.
- Disable the error alert on all cells so users can type search text without triggering validation errors.
This is a more advanced setup. For the complete step-by-step walkthrough with screenshots, see our dedicated guide: How to Create Searchable Drop-Down Lists for Every Row.
Frequently Asked Questions
Is there a built-in searchable dropdown in Excel?
Yes. In Microsoft 365 (Windows, Mac, and mobile), every Data Validation dropdown list includes autocomplete. Start typing in the cell and Excel filters the list automatically. This feature is not available in Excel 2019 or Excel 2021 (one-time purchase versions).
Why is my dropdown autocomplete not working in Microsoft 365?
The autocomplete feature requires you to type directly in the cell first, then click the dropdown arrow. If you click the arrow first and then try to type, the filter does not activate. Also verify that your Microsoft 365 subscription is up to date and that you are running the latest version (File > Account > Update Options).
Can I make a searchable dropdown without VBA?
Yes. In Microsoft 365, no setup is needed at all. For Excel 2021 or older, use the FILTER + SEARCH + ISNUMBER formula approach shown in this guide. It uses Dynamic Arrays and does not require any VBA code.
Does the FILTER method work with an Excel Table as the source?
Yes. Convert your source data to an Excel Table (Ctrl+T) and the FILTER formula will automatically include new rows added to the table. The Data Validation dropdown updates without any manual range adjustments.
Can I create a dependent searchable dropdown (second list depends on the first)?
Yes. You can combine the FILTER method with a dependent dropdown setup. The second dropdown’s FILTER formula includes an additional condition based on the first dropdown’s selection. For a full walkthrough, see Dependent Searchable Dropdowns for Every Row.
Download the Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
