Searchable Drop Down List in Excel (With FILTER Function) (2024)

Demonstrating the Solution

The solution we will build allows us to type in a collection of letters, click a dropdown arrow, and display a list of items that contain those letters anywhere in the item.

If we wish to see the full list, we erase any and all letters from the search cell and click the dropdown arrow. With no items to search for, the complete list is displayed.

Methods to Solve the Problem

Performing a quick Internet search for “searchable drop down lists in Excel” will return links taking you to websites demonstrating solutions using VBA. I have a similar solution using VBA that is covered in my using User Forms.

“I don’t want to use VBA to solve this dilemma”, you say?

Let’s look a solution to the searchable drop down list in Excel using a rather new feature; Dynamic Arrays.

💡If you’re subscribed to Microsoft 365, good news! An even simpler solution has been available since 2023. Discover more in our article 👉 HERE.

Formula Solution with Dynamic Arrays

Until recently, performing many advanced Excel operations required writing complex and lengthy formulas to feed elaborate data preparation tables. This was most likely the exclusive domain of the ultra-advanced Excel user.

Not any more! We will solve this problem using Dynamic Arrays.

❗ Dynamic Arrays are only available in Office 2021 and to Office 365 subscribers.

Creating the Solution

We wish to have the searchable dropdown list on the sheet named “Report” in cellB5in the sample file.

When the user searches for and selects a name from cellB5, we want the selected name’s company to appear in cellC5.

We have another sheet named “MasterData” that contains a list of all the customers and their associated companies.

This list is not an official Data Table, but later we will “upgrade” this plain table to an official Excel Data Table to leverage the ability to add new items to the table and have the rest of our solution update automatically.

Although we have the full list of customers/companies, we need to derive from that a filtered version that only contains items that match the search criteria supplied in cellB5.

If we type in the letters “GAR” in cellB5, we need a list generated that contains any and all items from the Customer column that contain the letters “GAR”.

A Minor Issue

Dynamic Arrays work well with named ranges stored in theName Manager.The issue is that Data Validation dropdown lists do not work with named ranges that contain Dynamic Array references.

Because of this restriction, we need to create a data preparation table that will cull the information from the Customer column and place it in columnDof the “MasterData”sheet.

Element #1 – Find the Text Using the SEARCH Function

NOTE: To simplify the demonstration, we will pretend that the Data Validation search list is in cellD1of the “MasterData” sheet. This will keep us from having to switch back-and-forth between sheets during the development of the solution.

When looking for all items that contain the letters “GAR”, enter the letters “GAR” in cellD1.

In cellD2, we will create a function to perform the search.

TheSEARCHfunction has three parameters:

• “Find_Text” – this is the text or cell holding the text to be located
• “Within_Text” – this is the cell or array of cells to be searched
• “[Start_Num]” – this is an optional parameter that allows you to define the character position within the “Within_Text” parameter you want to start searching. If this parameter is undeclared, the search begins at character position 1 (far left).

Our first test will be to locate the text in cellD1within the text located in cellA2.

=SEARCH(D1,A2)

We are presented with the result “1”.

This is because the letters “GAR” appears starting in the first character position counting from the left side of the data.

If we change the search criteria to the letter “M”, we are returned the number “6”, since the “M” occurs in the sixth character position of the name “Gary Miller”.

If we change the search criteria to something that does not appear in the search text, such as the letter “V”, we are presented with a#VALUE!error.

Let’s update our formula to include the full range of Customer names.

=SEARCH(D1, A2:A32)

The result is quite the attention getter!

This is the power of Dynamic Arrays.

Dynamic Arrays allow us to write a single formula but return many results. In this case, we wish to locate the text in cellD1within every item of the Customer list. The answers appear in cellsD2:D32.

This is known as a “spilled array”.

If we change the search criteria to “ROB”, we see that the letters appear in four of the Customer names, yielding numbers, and#VALUE!errors for all other list items.

Element #2 – Convert the Numbers/Errors to True/False Reponses Using the ISNUMBER Function

If the response to a search returns a number, we want to display the word “True”, and where there are errors, we want to display the word “False”.

Update theSEARCHfunction in cellD2to read as follows:

=ISNUMBER(SEARCH(D1, A2:A32))

We now have a list of True/False responses.

Element #3 – Filter the List to Display Only True Responses Using the FILTER Function

TheFILTERfunction is a new Dynamic Array function that has the following parameters:

• “ARRAY” – The list of items to filter.
• “Include” – The item to be located within in the array.
• “[If_Empty]” – An optional parameter of what to display if no items are returned from the filter, such as empty text or a message.

We need to update our formula in cellD2to filter the Customer column based on the examination previously performed by theISNUMBER/SEARCHformula. Since the formula is returning True/False response, any item with a True response will be included in the resultant list, while any item with a False response will be filtered from the list.

Update the formula as follows:

=FILTER(A2:A32, ISNUMBER(SEARCH(D1, A2:A32)), ”Not Found”)

Observe the result of the updated formula.

If we test this formula by entering different search criteria in cellD1, we are presented with different results.

If we search for something that is not in the list of Customers, like “Leila”, the formula returns the message “Not Found”.

Switching from Development to Production

CellD1on the “MasterData” sheet was our testing site for text to be searched. Our official search location is on the “Report” sheet.

Let’s erase what we have in cellD2on the “MasterData” sheet and place a title in its place, like “Data Validation Prep.”

We now need to update the reference in theSEARCHfunction to look for the data entered in cellB5of the “Report” sheet. Update the formula as follows.

=FILTER(A2:A32, ISNUMBER(SEARCH(Report!B5, A2:A32)), ”Not Found”)

If we place the text “GAR” in cellB5of the “Report” sheet, the data preparation table in columnDof the “MasterData” sheet updates accordingly.

Increased Functionality of the Dynamic Dropdown List

If the list of Customers in columnAof the “MasterData” sheet contained duplicate items, another function that could be incorporated into our logic is theUNIQUEfunction. Observe the following formula.

=UNIQUE(FILTER(A2:A32, ISNUMBER(SEARCH(Report!B5, A2:A32)), ”Not Found”))

This will produce a list of items without duplicates.

Another feature you may wish to incorporate is to have the list sorted, even when new items are added to the bottom of the source list. This is accomplished with the newSORTfunction. Observe the following formula.

=SORT(FILTER(A2:A32, ISNUMBER(SEARCH(Report!B5, A2:A32)), ”Not Found”))

Creating the Dropdown list from the Data Preparation Table

To create the Data Validation dropdown list, selectData (tab) -> Data Tools (group) -> Data Validation.

On theSettingstab in theData Validationdialog box, select “List” from theAllowdropdown.

In theSourcefield, enter select the first cell in the data preparation table on the “MasterData” sheet. Because we want to capture the entire spilled array that begins in cellD2, add a “#” pound (or hashtag) symbol to the end of the reference.

=MasterData!$D$2#

Tweaking Data Validation Settings

Currently, the Data Validation rules do not allow for the entering of information not previously defined in the source list.

If we enter something like “ROB” in cellB5, we are presented with the following error message.

To allow for the entering in of previously undefined data, and to suppress this error message, selectData (tab) -> Data Tools (group) -> Data Validation.

On the “Error Alert” tab, uncheck the option for “Show error alert after invalid data is entered”.

Now, if we enter the letters “ROB” in cellB5and click the dropdown button, we are presented with a list of items containing the letters “ROB”.

Returning the Company Information

As stated early on in this post, the objecting was to select a customer name from a dropdown list and return the customer’s company name.

We can achieve this with tried and true Excel functions likeVLOOKUPorINDEX/MATCH, but since we’re using the new Excel Dynamic Array function, let’s capitalize once again on theFILTERfunction.

On the “Report” sheet, click in cellC5and enter the following formula.

=FILTER(MasterData!B2:B32, MasterData!A2:A32-Report!B5, ””)

In English, this reads as follows:

Return an item from the Company column (columnB) where the name in the Customer column (columnA) matches the name placed in cellB5of the “Report” sheet. If the name is not found, return empty text (two double quotes).

Adding New Customers to the Master Data Sheet

To leverage the automatic inclusion of new information in our Customers table, we need to convert our traditional plain table into an official Excel Data Table.

Select any cell in the Customer/Company table and pressCTRL + T(orHome (tab) -> Styles (group) -> Format as Table -> select a style) and clickOK.

We will rename our table by selectingTable Tools – Design (tab) -> Properties (group) ->and enter the name “TableCustomers” in the Table Name field. This step is optional, but considered a best practice.

Because everything is already set with Dynamic Arrays, all we must do is add a new item to the Customer/Company list and test for that new item.

Our formulas automatically update to include the expanded range when we place new items in the Data Table list.

Practice Workbook

Feel free to Download the WorkbookHERE.