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 Excel dropdown lists” will return links taking you to websites demonstrating solutions using VBA. I have a similar solution using VBA that is covered in my VBA course using User Forms.
“I don’t want to use VBA to solve this dilemma”, you say?
Let’s look a solution to the searchable dropdown list using a new feature in Excel; Dynamic Arrays.
The Magic of 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.
As of the writing of this post (January 2019), Dynamic Arrays are only available to Office 365 subscribers on the “Office Insider” channel but will be available to all Office 365 user in the very near future.
Creating the Solution
We wish to have the searchable dropdown list on the sheet named “Report” in cell B5 in the sample file.
When the user searches for and selects a name from cell B5, we want the selected name’s company to appear in cell C5.
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 cell B5.
If we type in the letters “GAR” in cell B5, 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 the Name 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 column D of 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 cell D1 of 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 cell D1.
In cell D2, we will create a function to perform the search.
The SEARCH function 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 cell D1 within the text located in cell A2.
=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 cell D1 within every item of the Customer list. The answers appear in cells D2: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 the SEARCH function in cell D2 to 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
The FILTER function 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 cell D2 to filter the Customer column based on the examination previously performed by the ISNUMBER/SEARCH formula. 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 cell D1, 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
Cell D1 on 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 cell D2 on the “MasterData” sheet and place a title in its place, like “Data Validation Prep.”
We now need to update the reference in the SEARCH function to look for the data entered in cell B5 of 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 cell B5 of the “Report” sheet, the data preparation table in column D of the “MasterData” sheet updates accordingly.
Increased Functionality of the Dynamic Dropdown List
If the list of Customers in column A of the “MasterData” sheet contained duplicate items, another function that could be incorporated into our logic is the UNIQUE function. 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 new SORT function. 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, select Data (tab) -> Data Tools (group) -> Data Validation.
On the Settings tab in the Data Validation dialog box, select “List” from the Allow dropdown.
In the Source field, 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 cell D2, 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 cell B5, we are presented with the following error message.
To allow for the entering in of previously undefined data, and to suppress this error message, select Data (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 cell B5 and 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 like VLOOKUP or INDEX/MATCH, but since we’re using the new Excel Dynamic Array function, let’s capitalize once again on the FILTER function.
On the “Report” sheet, click in cell C5 and 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 (column B) where the name in the Customer column (column A) matches the name placed in cell B5 of 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 press CTRL-T (or Home (tab) -> Styles (group) -> Format as Table -> select a style) and click OK.
We will rename our table by selecting Table 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 Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
