Bottom Line: Learn how to create dynamic data search box in Excel that allows you to filter your data in real-time. Part 1 covers the basics of the FILTER function, multiple different search modes, and how to create dynamic search box from scratch and connect it to the FILTER function.
Skill Level: Intermediate
Video Tutorial
Filtering and searching big amounts of data is a regular task for many Excel users. Until today, there has been no smooth and simple way to search through data in real-time, but with the new FILTER function you can now create amazing real-time search boxes to filter your data as you type.
This tutorial is divided into 2 parts:
- Real-Time Data Search Box Basic [this tutorial]
- Multi-Column Search with Data Search Box
Info: The FILTER function is only available in Excel 365 (which is part of the Microsoft 365 subscription).
Introduction to the FILTER function
The new FILTER function in Excel 365 is one of six so-called dynamic array functions in Excel. Dynamic array functions allow you to type in your function into one cell and make the results spill into all surrounding cells with no additional effort.
The FILTER function is the perfect addition to the set of lookup functions in Excel. While the classical lookup functions can only return the results of one row from your data, the FILTER function returns multiple rows and results within seconds. You only have to learn how to define the filtering conditions correctly, and in this tutorial you will learn the most relevant simple to advanced filtering techniques with this function.
Let’s have a quick look at the syntax. The syntax is actually really simple:
=FILTER(array, include, [if_empty]
For the array argument you simply put the range of data you want to be considered and potentially returned. For the include argument you put in a logical test that returns a boolean (TRUE or FALSE) value for each row in your array. For the if_empty argument you you type in a message that is displayed if every row in your data failed the logical test in the include argument and, thus, no match is displayed.
In the next section you will learn how to define the logical test in the include argument to implement 3 powerful search modes (from simple to advanced).
3 Powerful Search Modes
Based on what kind of logical test you put into the FILTER function for the include argument, you can create simple or advanced search modes. As a foundation for the dynamic data search box you need to know these 3 different search mode types.
Exact Match
This is the most simple search mode you can use for the FILTER function. The logical test to test for exact matches looks simply like:
search_column = seach_term
In a real data example the FILTER function for this looks as follows:
=FILTER(A9:C21, A9:A21 = “Test”, “NO MATCH FOUND”)
With this formula you return each row of the array A9:C21 for which the cell in the Column A has the value “Test”. The problem with this simple approach is, if you put in or reference only a part of the word “Test” as your search term, none of your rows will be returned by the filter function. To also return rows with partial matches, you need a little more complex formula.
Partial Match
This search mode is bit trickier to implement, but it allows you to filter for all rows as long as they contain the search term at some point in the search column cell. The logical test to test for partial match looks like this:
ISNUMBER(SEARCH(search_column, search_term))
In a real data example the FILTER function for this looks as follows:
=FILTER(A9:C21, ISNUMBER(SEARCH(A9:A21, “Tes”)), “NO MATCH FOUND”)
With this formula you now return each row that has the search term “Tes” as part of its cell in column A. This is the required foundation for real-time search that displays any partial match as soon as you only type in parts of your search term.
Partial Left Match
This search mode is similar to the partial match, but it filters more strictly for rows in which the cell value begins with the search term. The logical test to test for partial match looks like this:
LEFT(search_column, LEN(search_term)) = search_term
In a real data example the FILTER function for this looks as follows:
=FILTER(A9:C21, LEFT(A9:C21, LEN(“Tes”))=”Tes”, “NO MATCH FOUND”)
With this formula you now return each row that has the search term “Tes” as at the beginning of its cell in column A.
Setting up a Dynamic Data Search Box
The first thing you need to do is, instead of manually typing in your search term into the FILTER function, reference a cell for putting in the search term. That could look like this:
=FILTER(A9:C21, A9:A21 = $F$2, “NO MATCH FOUND”)
Now you can go to Developer > Insert > ActiveXControls > TextBox to insert a Search Box. In order to connect it to your search cell F2, right-click on the Search Box and go to Properties. In the pop-up window you can now define a Linked Cell, for which you simply put in your Search Cell F2.
Now, every character you type into your search box will be directly entered (not only typed) into your search cell. That means, with every character you type your FILTER formula automatically updates and shows the values based on the filtering condition. Combine that with the partial match or partial left match mode and you are ready to filter as you type in real-time.
Conclusion
This first part of the tutorial teached how to create a dynamic data search box with real-time filtering from scratch, but it was limited to searching in only one fixed column. If you want to learn how to search in multiple columns or even with multiple search boxes, have a look at Part 2 of this tutorial.
For an instant insight into all techniques covered in this tutorial, download the worksheet from our Download Area.
