Too Many Choices? Let's make those Dropdowns searchable!

While featuring in Money Man's 24 track, Lil Baby explains his challenge of deciding which car to drive, as he has many options:

"Which one I'ma drive? I got sixteen options"

Like Lil Baby, you may have faced a challenge with your Dropdowns in Excel where there are just too many options:

In the Example above, we have over 100 different options to choose from, and not having them in any specific order is no help.

So how about creating a Search Box for the dropdown, that limits what options are available?

First, we'll dedicate a cell that the user can use to search.

Next, we'll use a blank cell, to do the calculation:

The formula should be as follows:

=FILTER(DataValidationRange,IFERROR(SEARCH(SearchTerm,DataValidationRange),0)>0)

So for my case, I am getting the SearchTerm from the G1 cell:

=FILTER(DataValidationRange,IFERROR(SEARCH(G1,DataValidationRange),0)>0)

I'm using a Table to source data for my dropdown, so my Data Validation Range is as follows:

=FILTER(Words[Interesting Words],IFERROR(SEARCH(G1,Words[Interesting Words]),0)>0)

Now, our formula is complete. The formula returns all relevant options for whatever is in the Search Box:

Now, we'll simply click where we want our dropdown, and point to the cell containing our formula followed by a # symbol.

And that's it! Now you have a Searchable Dropdown! Perhaps now you can find your lost love!