In the Dropdown List in Excel article, we went through the basics of setting up a dropdown list in Excel. This time we will go through how to make a searchable dropdown list and we will also sort the list in alphabetical order so it is easier to search through the list. And then at the end we will set up an even more complex dropdown list where the list of options changes depending on another cell and is searchable.

Creating a Searchable List

As an example above, let’s say there’s a very long list of entries in column A. Creating a searchable list will make it a lot easier to look through the column. To do this, we first use the SEARCH function to look for that keyword/key phrase in the list. And then we use the ISNUMBER function to see if each item in the list returns a TRUE response or a FALSE response. TRUE means it can be found. FALSE means it cannot be found. Then we use the FILTER function so that we only see the TRUE response list. And then at the end we use the SORT function to sort the end result in alphabetical order.

To make it easier to understand, have a look at example below:

The formula:

• =SORT(FILTER(A2:A34,ISNUMBER(SEARCH(F2,A2:A34)),”Not found”))

The formula looks for what is entered in F2 (Keyword) in the A2:A34 list. If anything can be found, the A2:A34 list will be filtered to return the new list. Tip: if there is no keyword or key phrase entered at all, the full list will appear. And if nothing can be found, the list will return with “Not found”.

Put this New Searchable List into a Dropdown List

To make this searchable list a dropdown list, it is simple. Select the cell where you want to create the dropdown list, go to Data at the top and select Data Validation:

Tip: Because we don’t know how many items will be returned based on the keyword or key phrase, the list is dynamic. We know the list starts with C2 and then we can add # after it. This way Excel will include everything from C2 to the last item in the list, whatever that is.

In the Error Alert tab, make sure the “Show error alert after invalid data is entered” is not ticked.

Now we have a dropdown list which is searchable.

Creating a Dynamic Searchable Dropdown List

Combining everything we’ve learned, we will now make a dropdown list where the list of entries changes depending on value of another cell and is searchable. Continuing on with the example above, in the real world a staff directory is usually separated by teams. So how can we have a dropdown list where the list of staff is different depending on which team is selected? And of course we want the list to be searchable.

For example: the first dropdown list allows you to select one of the teams that is available across Row 1 (A1:E1):

And the second list “Staff” changes depending on which team you’ve selected in J2:

To do this, we really only need to add one more step to above. Because there are now multiple lists available, we now need to create this one list which updates itself depending on which team is selected:

The formula is:

• =INDEX($A$2:$E$8,ROW($A$1:$E$8),MATCH($J$2,$A$1:$E$1,0))

This…looks ugly. The #REF does not matter because when making this list searchable, the #REF will return a FALSE result hence it won’t show up in the final dropdown list. But the 0 will if no keyword/key phrase is added – because the full list appears when nothing is entered. To fix this, we just need to add an IF formula. IF a 0 is returned, give us blank “” instead.

• =IF(INDEX($A$2:$E$8,ROW($A$1:$E$8),MATCH($J$2,$A$1:$E$1,0))=0,””,INDEX($A$2:$E$8,ROW($A$1:$E$8),MATCH($J$2,$A$1:$E$1,0)))

As mentioned, it doesn’t matter if the #REF is there. But if you like to see a cleaner list, you can use the IFERROR formula so that any error will also return “” – blank.

The next step is to make this new list searchable. Here we follow the exact same steps as what we’ve shown in the beginning of this article. First we search for what is entered in K2 in the new G2:G8 list. Then we see if a TRUE or FALSE response is returned. And then we filter to have a list with the TRUE responses. At the end we sort the list alphabetically.

Again, we need to go into the Error Alert tab and make sure the “Show error alert after invalid data is entered” box is not selected.

To make the file look cleaner, you can hide column G and H. Sometimes we set this up in a separate worksheet tab and protect the sheet so no one would accidentally change or delete the formulas. For information on how to protect a worksheet, you can check out How to Password Protect an Excel File.

We hope we have made it clear on how to make a searchable dropdown list and how to have the list changed depending on the value of another cell. If anything is not clear, feel free to leave a comment below!