Searchable Drop Down List in Excel - Excel Tips and Tricks

Learn how do I create a searchable drop-down list in Excel. This will ultimately answer your question on can we search in drop-down list in Excel, or how do I make a drop-down list searchable in Excel? Some ask it like how do I create a searchable Data Validation list in Excel? This video will teach you how do I create a dynamic list in Excel. This video also answer question on how to create a searchable drop-down list in Excel Office 365? This will work on Microsoft Office 2021 as we will be using the FILTER() and UNIQUE() function.


Here are the formulas outlined in my video. I have listed them in a Lego building block format for your clear and concise understanding.

=SEARCH(E1,D2:D98)

=ISNUMBER(SEARCH(E1,D2:D98))

=FILTER(D2:D98,ISNUMBER(SEARCH(E1,D2:D98)),"NA")

=SORT(FILTER(D2:D98,ISNUMBER(SEARCH(E1,D2:D98)),"NA"))

=UNIQUE(SORT(FILTER(D2:D98,ISNUMBER(SEARCH(E1,D2:D98)),"NA")))

This is the final formula.
=IF(E1=0,D2:D98,UNIQUE(SORT(FILTER(D2:D98,ISNUMBER(SEARCH(E1,D2:D98)),"Not Found"))))

Here are the steps outlined in my video.

Searchable Drop Down List
1) Write Formula
2) Associate B2 with E1
3) Data ~ Data Tools ~ Data Validation ~ Data Validation
6) Allow set to List
7) =$E$2#
8) Error Alert tab
9) Remove check on "Show error alert"
10) OK

Let's breakdown the formula.

=IF(E1=0,D2:D98,UNIQUE(SORT(FILTER(D2:D98,ISNUMBER(SEARCH(E1,D2:D98)),"Not Found"))))

If E1 is empty, it displays all names. Otherwise, it performs various functions like UNIQUE, SORT, FILTER, etc. Let's start with the innermost function.

SEARCH() returns the position of the first appearance of the text in cell E1. This enables inclusion of the name in the dropdown list. If the text in E1 is not found, it returns the error #VALUE!.

The ISNUMBER() returns a TRUE if SEARCH() function returns a numeric value.

FILTER() function is utilized to filter the range D2 to D98 based on the criteria returned by ISNUMBER() function. If the criteria are TRUE, it returns a name; otherwise, it returns "Not Found".

SORT() will sort it in alphabetic order.
UNIQUE() will include distinct values in the drop down.


How do I create a searchable drop-down list in Excel?,Can we search in drop-down list in Excel?,How to create a searchable drop-down list in Excel Office 365?,
How do I make a drop-down list searchable in Excel?,How do I create a searchable Data Validation list in Excel?,How do I create a dynamic list in Excel?,