Standard Excel drop-down lists can be difficult to navigate when they contain hundreds of items. By using the FILTER function, you can create a dynamic, searchable list that updates as you type.
Gör det med AIPrepare your source data
Ensure your list of items is in a clean column and define a cell (e.g., B1) where you will type your search term.
Create the filtered list
In a nearby cell, use the FILTER function to return items that match your search term, wrapping it in SORT for better organization.
Apply Data Validation
Go to the Data tab, click Data Validation, select 'List', and set the source to the cell containing your dynamic FILTER formula followed by the '#' symbol.
=SORT(FILTER(SourceRange, ISNUMBER(SEARCH(SearchCell, SourceRange)), "No results"))The '#' symbol tells Excel to reference the entire 'spilled' range generated by the dynamic array formula, rather than just the first cell.