This guide will teach you how to use the FILTER and SEARCH functions to create a searchable dropdown list in Microsoft 365 or Excel Online.
Step 1: Implementing the SEARCH Function
First, we use the SEARCH function to check if what you type is in the client list. This looks through the entire column and returns the position of a match.
- If a match is found, SEARCH returns the position.
- If no match exists, the function returns #VALUE!
Step 2: Convert to a Logical Test
We don’t need the actual position from the SEARCH function—just a simple YES (TRUE) or NO (FALSE) if there’s a match. The ISNUMBER function simplifies this by converting SEARCH results into a clear logical test.
=ISNUMBER(SEARCH(D1, A2:A30))
Step 3: Filter the List
To extract only the matching names, we apply the FILTER function. FILTER retains values where its second argument evaluates to TRUE. Therefore, we directly insert the logical test formula from the previous step as the function’s second argument.
Step 4: Managing Error Handling
If no matches are found, we can show a custom message instead. The third argument in FILTER lets us display a message without results.
The formula works just as expected! Now, we need to create a dropdown list that uses it. We'll need a workaround since Excel doesn’t allow formulas directly as a dropdown source.
Step 5: Create the Dropdown List
- Select the cell where you insert your searchable string, D1 in this example
- Open Data > Data Validation to create your dropdown list.
- Choose List as the validation type.
- In the Source box, enter the cell reference where your formula is.
- Add # at the end to include all results from the FILTER formula.
- Go to the Error Alert tab
- Uncheck the Error Alert. Otherwise, you will see an error each time you type any characters
Conclusion
And that’s it! 🎉 You’ve now built a searchable dropdown list in Excel that updates as you type. With the FILTER function, your dropdown stays dynamic and always reflects matching results. Try it out and make your Excel sheets even smarter! 🚀
