Searchable Drop Down List in Excel

(Very Easy with Dynamic Arrays)

“Does Excel have a feature to allow a Data Validation list to be searchable?”

This is a common question asked on this site and most any Excel training venue.

What do we mean by a searchable Data Validation list?  Let’s look at the example below.

A traditional dropdown list provided via the Data Validation tool displays a list of items from a predefined list.  The issues with the list are multifold:

  • The list is not sorted; i.e. if new items are added to the list, the list does not automatically re-sort.
  • The list is not searchable. Locating a specific item in an unsorted list can be both tedious and time consuming, especially when the list contains hundreds or thousands of items.
  • The list does not automatically suppress duplicate items. If an item exists multiple times in the source list, the dropdown list will display all items regardless of repetition.

Although all the above-mentioned issues are worth investigating, this post’s focus is on the searchable aspect of the Data Validation list.

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.

Excel Download Practice file

Excel NEW Dynamic Arrays Course 

BE THE FIRST to master Dynamic Arrays

From Beginner to Expert Level!

Start Learning

36 Comments

  1. Kanthsmith January 26, 2019 at 5:11 pm - Reply

    Were can I get that filter formula in excel workbook

    • Leila Gharani January 28, 2019 at 12:04 pm - Reply

      If you have office 365 you will get it in a future update. Currently it is available in the Insider version.

    • Amit April 4, 2019 at 6:27 am - Reply

      For study nice

  2. Ravi January 28, 2019 at 9:17 am - Reply

    Hi Leila, I could not find the FILTER and SORT function. I am using Office 365 proplus ver.1803.

    Thanks!
    Ravi

    • Leila Gharani January 28, 2019 at 12:03 pm - Reply

      Hi Ravi – my version in the video is 1902. This is the Insider version. You will get the new functions once it’s rolled out in a future update.

      • Ravi January 29, 2019 at 3:07 am - Reply

        Noted. Thanks for the reply Leila.

  3. Sandy February 8, 2019 at 4:09 pm - Reply

    Hi Leila,

    When I copy your step,after I put in filter function, the cell show ‘!spill’. What possible wrong thing I did?

    Beside, if the office don’t have the filter function yet, is there any other opinion to do that?

    Thanks,
    Sandy

    • Leila Gharani February 9, 2019 at 6:13 pm - Reply

      #Spill error means something is blocking it in the cells below. Remove any values in the cells below and your formula will work. Unfortunately it’s not easy to set up in Legacy Excel – you need to use names in name manager and few more steps. An alternative would be a VBA approach.
      It’s great that you have dynamic arrays : )

  4. saiful February 14, 2019 at 11:53 am - Reply

    Same issue in my office 365, there is no filter function, is there any way forward

    • Leila Gharani February 17, 2019 at 5:35 pm - Reply

      It’s not officially available yet – it’s in the insider edition of 365 for now…

  5. Usman Qasim February 27, 2019 at 12:07 pm - Reply

    Hi, Leila Gharani,
    Really nice content you share and really easy steps but the problem is FILTER formula is not available in Office 365 or Office 2019. so please share some other way to how we have to make this dropdown list.

    • Bryon Smedley April 7, 2019 at 8:18 pm - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      XelPlus Team

  6. Vinod Rampuria March 4, 2019 at 5:54 am - Reply

    A. I have an Excel Table having a) full names of Policy Holders with spaces in between as Column Headers, and b) Policy numbers as rows under each name of Policy Holder for the policies they are holding.
    My object was to get a dynamic dependent list of policies when I choose names from the List of Policy Holders.
    I have used for dependent list data validation condition the formula:
    “=OFFSET(INDIRECT(SUBSTITUTE(D3,” “,”_”)),0,0,COUNTIF(INDIRECT(SUBSTITUTE(D3,” “,”_”)),”?*”),1)”
    where D3 is the selected Name from the parent Policy Holder drop down list. this is for replacing every Policy Holder’s name with underscore replacing the spaces between their full names.
    But before this data validation works, I had to Define Name for every Policy Holder’s in the Header of the Table with underscore replacing the spaces between his name.
    So, every time I have to add a new Policy Holder by expading the Table, I have to go to Define Name and add the new name with underscore replacing spaces. This restricts my Application to be dynamic. Can you suggest a better approach.
    B. I want to display the first available Policy number under a chosen Policy Holder immediately before the dropdown Dependent List is displayed. Can you suggest a solution.

    • Bryon Smedley April 7, 2019 at 8:11 pm - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      XelPlus Team

  7. Dan March 17, 2019 at 8:55 pm - Reply

    How would you go about implementing this type of “searchable” data validation when you need to have the same functionality across many cells in a column? In this example, you are only ever using one cell’s inputs, meaning that one “Data Validation Prep” column is sufficient; however, if you want to apply the same functionality to multiple cells, is the only solution to build out multiple “Data Validation Prep” columns?

    • Leila Gharani March 28, 2019 at 5:59 pm - Reply

      I think this might be possible with name manager and formulas once Microsoft fixes the current bug (dynamic arrays can be used in names but names with dynamic arrays cannot be used in data validation). Otherwise VBA would probably be the easiest. I show this version inside my VBA course.

  8. GF March 28, 2019 at 3:15 pm - Reply

    Hi Leila,

    First off love the way you step through the building of the formula, really helpful and thank you.

    I too do not have the FILTER function, but before that step the array does not “Spill” for me? I copied your spreadsheet exactly to see it may have been something to do with the data I was using but it still did not spill. I can drag the formula and it works, but it did not spill automatically (tried it as both a normal formula and an Array (CTL/SHIFT/ENTER)).

    Is this because I am awaiting the update, although I just checked and I seem to have Version 1902 (11328.20222 Click to Run).

    Is there some other reason the SPILL is not working, am I missing something obvious?

    Thanks
    GF

    • Bryon Smedley April 7, 2019 at 7:41 pm - Reply

      Microsoft is addressing some issues with Dynamic Arrays prior to widespread release. Unless you are a member of the Office Insider’s group, it will be a bit longer before it is released. Believe me when I say, it’s better to wait on a stable release than to risk developing unstable solutions or breaking existing solutions.

      Thanks – XelPlus Team

  9. LA March 31, 2019 at 9:50 pm - Reply

    Hi miss Leila Gharani,
    Thank you for your article about dynamic array’s.
    I got a thing to ask.
    Between two image you wrote “The result is quite the attention getter!”
    What did you do between those two images?
    If I ctrl-shift-down and enter or shift-ctrl-enter, or whatever I try, I don’t get any correct results.
    Btw using a mac excel 16.24v1903….
    It is just about that part what I would like to know.

    If I jump over that part and go straight to the FILTER command then from there every thing is working fine.

    Also an issue, maybe a Mac thing, when I use the filter function in C5 for the company name and I do have a name/company double in the list ( like ‘James Wilard/Blend’ ), under the company the second/double company name will show up. Of course I could use unique again. Or not using FILTER over there. Is this the same in windows, did you notice that?

    Second issue, I have to press return in the validation cell and then I can press the validation/popdown Icon right at the cell to see a filtered and updated list. Also a Mac issue?

    • Bryon Smedley April 7, 2019 at 7:20 pm - Reply

      The only action taken between the two images you reference is the pressing of the ENTER key. That is why it is such “an attention getter”. Dynamic Arrays build the entire list without any user intervention.

      If I understand your question regarding the company name showing up twice in a dropdown list; I believe you would need to use the UNIQUE command there to reduce the list to have no duplicates.

      I am unclear as to your issue with the Mac version about having to press return to get an updated validation list. Assuming Excel is set to Automatic Calculation, this should occur without user intervention. If not, it may be a Mac issue.

  10. Arthur April 18, 2019 at 12:24 pm - Reply

    I also don’t have the FILTER function. Is the FILTER function available for Excel 2010? Or will it be available in future?

    • Bryon Smedley April 19, 2019 at 12:54 pm - Reply

      The FILTER function is part of the Excel 365 function library. There are no plans of releasing it to prior versions of Excel. 2016 or earlier.

      Sorry to be the bearer of bad news. Looks like you’ll need to upgrade if you want the function.

      Thanks – The XelPlus Team

  11. Simon Kerr April 22, 2019 at 3:48 pm - Reply

    https://www.xelplus.com/searchable-drop-down-list/
    I do not have the FILTER FORMULA in my EXCEL 2019
    Is there a work around??

    • Bryon Smedley April 22, 2019 at 6:30 pm - Reply

      Sorry. The FILTER function, along with the Dynamic Array functions are only available to Office 365 subscribers. It has been stated that there is no intention of rolling this out to stand-alone installations of Office 2019 or earlier.

      Gotta’ love Microsoft marketing strategies.

      The XelPlus Team

  12. Adarsh Hirjee May 26, 2019 at 8:29 am - Reply

    EXCELLENT…. alas no FILTER :-(

  13. Mukesh Mewara May 29, 2019 at 3:58 pm - Reply

    That function isn’t valid message appears

    • Leila Gharani May 30, 2019 at 6:00 pm - Reply

      It’s still not live in office 365. Hopefully soon though.

  14. Christian Jung June 19, 2019 at 10:34 am - Reply

    Hi there,

    does anybody of you insiders see signs of an imminent release of this dearly awaited feature? I mean it is 9 month after the initial announcement and 5 month after this nice post…

    Thanks,
    Christian

    • Leila Gharani June 19, 2019 at 2:34 pm - Reply

      Unfortunately we’re also not told in advance. We just know “it will come soon”. It’s really frustrating – I know….

  15. Ann June 26, 2019 at 7:51 am - Reply

    How do you apply searchable drop down for several cells in a specific column?

  16. Muhammad July 7, 2019 at 12:34 pm - Reply

    Hello,
    I am using Office 2016. There is no sort and filter function found. Which office version have this function.

    Thanks in Addvance.

    • Leila Gharani July 8, 2019 at 11:46 am - Reply

      These functions are in Office 365 version of Excel. Currently only in the insider version and soon available for the general public.

  17. Ettore July 11, 2019 at 5:39 pm - Reply

    I’m an Insider, using version 1907 – Build 11901.220038

    And still can’t use those formulas, i looked for the update logs on Office Website and couldn’t find those releases…

    See the link below:

    https://insider.office.com/en-us/releasenotes/pc/fast

    Can you help me? =)

    Thanks in advance!!

    • Leila Gharani July 18, 2019 at 7:38 am - Reply

      Microsoft is rolling it out now to Monthly Targeted channel which should be the version you have listed. You should have it by now, or will get it soon as they flight the update.

  18. Ken Nordin July 28, 2019 at 4:55 pm - Reply

    I downloaded the Practice Workbook and after enabling the Edit message, I entered ‘gar’ into the name serch cell. Nothing happens. I then looked at the fromulas and found ‘_xlfn._xlws.’. Whats this about?

    =_xlfn._xlws.FILTER(MasterData!B2:B32,MasterData!A2:A32=Report!B5,””)

    and

    =_xlfn._xlws.SORT(_xlfn._xlws.FILTER(A2:A32,ISNUMBER(SEARCH(Report!B5,A2:A32)),”not found”))

    I am using Office 365 Business Version 1906 Build 11727.20244

    Thank you!

    • Leila Gharani July 31, 2019 at 3:30 pm - Reply

      You don’t have the new features yet. It will come in the next updates…

Leave A Comment

Share This