Bottom Line: Learn how to create dynamic data search box in Excel that allows you to filter your data in real-time. Part 2 covers the implementation of searching in multiple columns and with multiple search boxes at once.
Skill Level: Intermediate
Video Tutorial
Disclosure: Some of the links below are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
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
- Multi-Column Search with Data Search Box [you are here]
Info: The FILTER function is only available in Excel 365 (which is part of the Microsoft 365 subscription). Learn about all new features and the pros and cons of upgrading your Excel version today in our complete Review of Excel in Microsoft 365.
Let’s add some multi-column search functionality to the data search box.
Table of Contents
Dynamic Data Search Box with single fixed Search Column
In part 1 of this tutorial, you have learned the basics of dynamic data search boxes with the FILTER function. The limitation of part 1 was, that we used a absolute cell references for the return array and the search column. Adjusting the search column with these approach would require a manual replacement of the respective part in the formula every single time.
This is why we cover 3 amazing tricks and techniques to add dropdown column selection, multi-column search, and even search with multiple search boxes at once to the dynamic data search box.
Preparation for improved Column Selection
As preparation for these 3 advanced techniques, you need to put your data into a table. This allows you to reference your data and search column by the table and column names instead of absolute cell references. For the following examples, we will assume that your data table has the name “DataTable” and you have 2 relevant search columns.
Implementing multi-column search techniques
Technique 1: Dropdown Search Column Selection
For this technique you need to create a dropdown list with all the column names in it. In your FILTER formula, you then have to dynamically reference the dropdown cell (let’s say it is G2) with the selected column name. To include this name as text into your formula, you need the INDIRECT function and adjust the formula as follows for the respective search mode (we assume the search term is entered into F2 via the dynamic search box):
EXACT MATCH:
=FILTER(DataTable, INDIRECT(“DataTable[” & $G$2 & “]”) = $F$2, “NO MATCH FOUND”)
PARTIAL MATCH:
=FILTER(DataTable, ISNUMBER(SEARCH(INDIRECT(“DataTable[” & $G$2 & “]”), $F$2), “NO MATCH FOUND”)
PARTIAL LEFT MATCH:
=FILTER(DataTable,LEFT(INDIRECT(“DataTable[” & $G$2 & “]”), LEN($F$2)) = $F$2, “NO MATCH FOUND”)
Technique 2: Multi-Column Search with Checkboxes
For this technique you need to have a cell with a boolean value (TRUE or FALSE) for each column you want to potentially include as search column. These boolean values serve as signal variables to include or exclude the respective column in your search. To control these boolean values with a nice UI element, we recommend to insert checkboxes and connect them to the respective cell via the Properties > LinkedCell option.
In your FILTER formula, you then have to create a logical test for each potential search column and multiply that logical test with the respective signal variable, and then add all single expressions up with the ‘+’ symbol. Let’s say the two relevant search columns are called “Name” and “Region”. The signal variable values for these are in the cells K3 and K4. Then the formulas for the respective search mode would look like this.
EXACT MATCH:
=FILTER(DataTable, K3 * (DataTable[Name] = $F$2) +K4 * (DataTable[Region] = $F$2), “NO MATCH FOUND”)
PARTIAL MATCH:
=FILTER(DataTable,K3 * ISNUMBER(SEARCH(DataTable[Name], $F$2)) + K4 * ISNUMBER(SEARCH(DataTable[Name], $F$2)) , “NO MATCH FOUND”)
PARTIAL LEFT MATCH:
=FILTER(DataTable,K3 * (LEFT(DataTable[Name], LEN($F$2)) = $F$2) + K4 *(LEFT(DataTable[Region], LEN($F$2)) = $F$2) , “NO MATCH FOUND”)
Technique 3: Multi-Column Search with multiple Search Boxes
For this technique you need to create a separate search box for each search column you want to search in first. Each search box has to be linked to a new search cell, so e.g. you can link the second search box to F3 instead of F2.
In your FILTER formula, you then have to create a logical test for each potential search column, but now, each logical test has to be compared against a different search cell. In our example we want to have a search box for the ‘name’ and the ‘region’ column, so we compare the ‘name’ column against the value of F2 (which is linked to the first search box) and the ‘region’ column against the value of F3 (which is linked to the second search box).
To make the search boxes work together, you need to multiply the logical tests with each other. Then the formulas for the respective search mode look like this.
EXACT MATCH:
=FILTER(DataTable, (DataTable[Name] = $F$2) * (DataTable[Region] * $F$3), “NO MATCH FOUND”)
PARTIAL MATCH:
=FILTER(DataTable,ISNUMBER(SEARCH(DataTable[Name], $F$2)) * ISNUMBER(SEARCH(DataTable[Name], $F$3)) , “NO MATCH FOUND”)
PARTIAL LEFT MATCH:
=FILTER(DataTable,(LEFT(DataTable[Name], LEN($F$2)) = $F$2) * (LEFT(DataTable[Region], LEN($F$3)) = $F$3) , “NO MATCH FOUND”)
Conclusion
This second part of the tutorial demonstrated how to implement advanced multi-column search with dynamic data search boxes. This makes filtering your data in real-time even more powerful and allows you to impress your colleagues and clients whenever it is important to apply an effective data filtering.
For an instant insight into all techniques covered in this tutorial, download the worksheet from our Download Area.
