Dynamic Search as you Type
If you want to search through data and have results updated on each keystroke, you probably won't think of Excel. But all your data is in Excel, so you should probably see if it's not only possible but easy.
At the start of 2020 it was possible by difficult, but during the course of the year Microsoft made it much, much easier by releasing into Excel 365 a number of functions.
In this tutorial you will:
Download sample workbook
This sample workbook contains a table of client and little else. But with little effort on your part you will create a dynamic search function where the results are updated as you type. The search will cover multiple columns with a single search string, and you'll be able to present the results in the order you wish, and in the numbers you want.
Download the sample workbook now.
Dynamic Search
.xlsx
Download XLSX • 74KB
Open the sample workbook now and select the Clients worksheet The Clients worksheet contains a list of 1,000 clients, each identified by the year they became clients and a sequential number. Each client is described by a name, and address and an email.
This tutorial will focus on the FILTER worksheet. Select the FILTER worksheet, now.
Learn about arrays and dynamic ranges
Select the Formula Menu, Click Name Manager, and set the Filter to, "Defined Names"
Please pay special attention to the 'Dynamic Formula' and 'Dynamic Range' entries, and the cells to which they refer. You will see that they both refer to the same cell (E6) but there's a '#' at the end of the 'Dynamic Range' reference.
The '#' means that the range isn't a single cell, but is dynamic, and during the course of this tutorial you will see the size of 'Dynamic Range' change.
All of these named ranges will be used in this tutorial, but mostly you'll be entering a formula into the 'Dynamic Formula' cell which is just below the ID header, and will always be the top-left cell of the dynamic range.
Entering the following dynamic formula (into cell E6)
=SEQUENCE(10)SEQUENCE is an array function, which means that instead of returning a single value, it returns an array of values. The '=' instructs Excel to show that array as a dynamic range in the worksheet, and in so doing Excel spills those values into the rows below. A thin blue outline shows the border of the dynamic range. You will not be allowed
Select a spill-over cell. A spill over cell is one of the cells in the dynamic range, but not the one at the top-left in which the formula was written. You will see the formula you typed into the 'Dynamic Formula' cell, but greyed out.
Try to delete the contents of a spill-over cells. Although the spill over cells appear to hold values, they are actually empty, and so deleting their contents has no visible effect. And the first time you try to delete such a cell you'll get an error message explaining why.
Enter, "Occupied", into any one of the spill-over cells. You can, of course, enter new values into the spill over cells, but with dynamic ranges Excel expects those cells to be empty, and if they're not a '#SPILL!' error will arise in the 'Dynamic Formula' cell. Delete, "Occupied", to clear the error.
In cells C9 and C10, enter the following two formulae
=rows(DynamicRange)=columns(DynamicRange)These functions count the number or rows and columns in a given range, and should indicate that the Dynamic range currently has ten rows and one column.
Enter the following dynamic range formula in turn:
=SEQUENCE(5,2)=SEQUENCE(1,4)All arrays are two dimensional, but if the number of columns is omitted then SEQUENCE assumes the number of columns to be one. Note the row and column counts in cells C9 and C10 change as the dynamic range changes size.
Enter the following dynamic range formula in turn:
=SEQUENCE(10,1,101)=SEQUENCE(10,1,5,-1)The third and fourth parameter of the SEQUENCE function identify the first number generated, and the increment (or step) between the numbers. Both default to one if omitted.
Create a filtered view of clients
Expand the formula bar and enter the following dynamic range formula.
=FILTER(
tblClient,
ISNUMBER(SEARCH(SearchString,tblClient[ID])),
"No records"
)You should see a list of seven clients, all of whom have an ID that includes the search string., "/0001". FILTER is another array function, which in this case extracts date from tblClient, but only if the search string is present in the ID. The final parameters provides text to show when no records match the filter criteria. Enter "XXX" in the search string to see this work,
It's not particularly useful only to be able to search on an ID, searching on a name, address or email would me more useful, and we can extend the FILTER to add conditions. But this formula is going to become extended much more than just that as we work through this example, and for clarity and the avoidance of deep nesting and unbalanced parentheses, we will first rewrite the formula using the LET function.
Rewrite the formula using the LET function as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])),
FILTER(tblClient, MatchID, "No records")
)There should be no visible change when you rewrite the formula as above, because the logic is unchanged. The LET function is new and the post, "Excel's LET function on the rise", explains this in some detail. But for now you should understand that we're adipting it in this tutorial because there's quite alot of work to go into this formula, and it will be far easuer to use the LET function than the alternative, which would involve deep and complex nesting of functions.
In the formula we've just typed, we declare a 'variable' called 'MatchID' (that will be TRUE for matching rows only) and then we use that 'variable' as the condition in the FILTER statement which, being the last parameter, is the one LET will return as the result of the calculation.
From now on in this tutorial, when you're asked to edit the dynamic formula, for your convenience the parts of the formula that do not change will be written in light grey.
Edit the dynamic formula as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])) ,
MatchName, ISNUMBER(SEARCH(SearchString,tblClient[Name])) ,
MatchAddr, ISNUMBER(SEARCH(SearchString,tblClient[Address])) ,
MatchEmail, ISNUMBER(SEARCH(SearchString,tblClient[Email])) ,
MatchAny, MatchID + MatchName + MatchAddr + MatchEmail ,
FILTER(tblClient, MatchAny, "No records")
)We now have a variable for each of he four columns we're searching, and the MatchAny variable (used as the condition in the Filter function) is logical 'or' that will be true if a match is found in any one of the four columns.
Excel's OR function doesn't work as a condition in the FILTER function. And so arithmetic is used instead. The four 'IsNumber' functione each returns 0 for false or 1 for true. When they are added together a non-zero total will mean that one or more of the component tests is true, and any non-zero value, like one, is interpreted as 'true'. Therefore plus (+) provides a logical 'or' operator, and multiply (*) provides a logical 'and' operator.
Enter the following values in the Search String to check whether it is working correctly
"/0001"
"Chris"
"Idaho"
".edu"
It's possible, by now, that you may have mistyped something and the formula has failed to deliver the result you expected. To make it easy to find the cause of unexpected results we're going to make a small change.
Edit the dynamic formula as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])) ,
MatchName, ISNUMBER(SEARCH(SearchString,tblClient[Name])) ,
MatchAddr, ISNUMBER(SEARCH(SearchString,tblClient[Address])) ,
MatchEmail, ISNUMBER(SEARCH(SearchString,tblClient[Email])) ,
MatchAny, MatchID + MatchName + MatchAddr + MatchEmail ,
Filtered, FILTER(tblClient, MatchAny, "No records") ,
Filtered
)Here, instead of returning the FILTER function as the output of the LET function, we are assigning it to the variable name 'Filtered'; and then we're returning that as the output of the LET function. There is no visible effect of this change within the Dynamic Range. But unexpected results will be easier to track down.
Edit the final parameter of the LET function to each of the intermediate variables in turn. On completion be sure to set the final parameter back to 'Filtered'.
Update results as you type
To have the dynamic range respond to each keystroke we need to update the SearchString cell with each keystroke. And this means we need an Active X control.
If you can't see the Developer menu in the Ribbon, then you will need to switch it on, which you can do through the Excel Options dialogue.
Enable Developer menu in ribbon (if necessary)
Select 'File' in the Ribbon
Click 'Options' at the bottom of the left panel
Click, 'Customize Ribbon' in the left panel.
Tick 'Developer' checkbox, as shown
Click OK
Insert Active X text box
Select the Developer Menu
Click the Insert button in the 'Controls' group
Under, "Active X Controls"
Select the Text Box, which is the 5th icon.
Draw a box somewhere above the Search String
Link Active X text box to search string
Right click on the control and select Properties.
Set the LinkedCell property to SearchString and close the properties Window.
Switch Design Mode Off
Change the text in the Active X control and observe the behaviour
Position Active X text box over search string
Switch Design Mode ON
Resize and reposition Text box so that it obscured the SearchString cell
Switch Design Mode Off
Can't save workbook macro-free?
After having setup an Active X control it's possible you may have difficulty saving your workbook, "macro-free". In all likelihood you've double-clicked the ActiveX controls whilst in Design mode, which causes Excel to think you're writing a macro. If this happens, please read the post, "Solved | Can't save workbook macro-free".
Sort results by client name
The search results are presented in the same sequence they exist in the client table, but it would be better if they were presented in order of client name. There's surprisingly little code required to achieve this
Edit the dynamic formula as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])) ,
MatchName, ISNUMBER(SEARCH(SearchString,tblClient[Name])) ,
MatchAddr, ISNUMBER(SEARCH(SearchString,tblClient[Address])) ,
MatchEmail, ISNUMBER(SEARCH(SearchString,tblClient[Email])) ,
MatchAny, MatchID + MatchName + MatchAddr + MatchEmail ,
Filtered, FILTER(tblClient, MatchAny, "No records") ,
Sorted, SORT(Filtered,2),
Sorted
)Little explanation is required here. We simply use the SORT function to create an array based on 'Filtered' (sorted on column 2); assign that to the vairiable 'Sorted'; and return that as the array to present on the worksheet.
Restrict number of rows
The number of rows in the dynamic range can be as large as the table itself (currently 1,000 row). Perhaps you have only limited amount of space to work with and going beyond, say, 20 rows, the dynamic range would spill over other important data, or perhaps you feel the user would be more comfortable seeing less than the complete result set. Because the SORT and FILTER functions are so fast, performance issues are unlikely, but if the results set is very large then you can experience a lag between pressing a key and seeing the results update, and so that might be another reason to restrict the number of rows show.
Add a Spinner Button ActiveX control
Select the Developer Menu
Click the Insert button in the 'Controls' group
Under, "Active X Controls"
Click the Spinner Button, which is the 7th icon.
Draw the Spinner button to the right of the text, "Show first 20 clients"
Set the Spinner button properties
Right click the Spinner Button and Select Properties
Enter, "PageSize", in the Linked Cell property
Enter, "5", in the Min property
Enter, "20", in the Max property
Enter, "5", in the Small Change Property
Close the Properties window
Turn Design Mode Off
The spinner button now controls the number of clients to show, but of course, the dynamic formula does not yet reference that PageSize. So we will now use the INDEX function (yes, that's an array variable, too) to cut a segment of the correct size from the Sorted array.
Edit the dynamic formula as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])) ,
MatchName, ISNUMBER(SEARCH(SearchString,tblClient[Name])) ,
MatchAddr, ISNUMBER(SEARCH(SearchString,tblClient[Address])) ,
MatchEmail, ISNUMBER(SEARCH(SearchString,tblClient[Email])) ,
MatchAny, MatchID + MatchName + MatchAddr + MatchEmail ,
filtered, FILTER(tblClient, MatchAny, "No records") ,
sorted, SORT(filtered,2),
ShowRows, Sequence(PageSize),
ShowCols, {1,2,3,4},
ShowData, INDEX(sorted,ShowRows,ShowCols),
ShowData
)Set the page size to 20 and the Search string to "Idaho".
Although the correct data is shown, there are also errors on show when the search returns fewer rows than the page size. Clearly we need to be a little more sophisticated in terms of calculating the number of rows to show. And we do this by introducing setting 'DataSize' to the number of rows in the Filtered set, and that will be the number of rows we show when it's less than the page size.
Rewrite the Formula as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])),
MatchName, ISNUMBER(SEARCH(SearchString,tblClient[Name])),
MatchAddr, ISNUMBER(SEARCH(SearchString,tblClient[Address])),
MatchEmail, ISNUMBER(SEARCH(SearchString,tblClient[Email])),
MatchAny, MatchID + MatchName + MatchAddr + MatchEmail,
Filtered, FILTER(tblClient, MatchAny,"No Records"),
Sorted, SORT(Filtered,2),
DataSize, ROWS(Filtered),
ShowCount, MIN(PageSize,DataSize),
ShowRows, SEQUENCE(ShowCount),
ShowCols, {1,2,3,4},
ShowData, INDEX(Sorted,ShowRows,ShowCols),
ShowData
)Enter the String, "Zero", as the search string. There are no records in the table that match this string, and so we should get the string, "No Records". The problem here is that when there is just one row in the Filtered record set, it's that array we need to show rather than the results of the INDEX. Again, thanks mostly to the beauty of the LET function, this can be easily fixed.
Rewrite the Formula as follows:
=LET(
MatchID, ISNUMBER(SEARCH(SearchString,tblClient[ID])),
MatchName, ISNUMBER(SEARCH(SearchString,tblClient[Name])),
MatchAddr, ISNUMBER(SEARCH(SearchString,tblClient[Address])),
MatchEmail, ISNUMBER(SEARCH(SearchString,tblClient[Email])),
MatchAny, MatchID + MatchName + MatchAddr + MatchEmail,
Filtered, FILTER(tblClient, MatchAny,"No Records"),
Sorted, SORT(Filtered,2),
DataSize, ROWS(Filtered),
ShowCount, MIN(PageSize,DataSize),
ShowRows, SEQUENCE(ShowCount),
ShowCols, {1,2,3,4},
ShowData, INDEX(Sorted,ShowRows,ShowCols),
ResultSet IF(DataSize>1,ShowData,Filtered)
ResultSet
)Review this tutorial
In this tutorial you have seen:
Learned about arrays and dynamic ranges.
Used FILTER to extract rows where there's a match in the ID of the Client table.
Extended the FILTER criteria to cover name, address and email as well as ID/
Used an ActiveX Text Box to update the Search String as you type.
Use SORT to order the results alphabetically by name.
Used an ActiveX Spinner to change the number of rows on a page
Used SEQUENCE and INDEX to restrict the number of rows shown.
You have also seen how use of the LET function can help simplify the writing of complex formula; avoid avoid confusing nesting; and make it easier to find the caused of unexpected results.
Download finished sample workbook
You may well have followed the instructions above and finished the sample worksheet yourself, but everyone's learning choices are different and so here for your convenience is one I finished earlier.
Dynamic Search Finished
.xlsx
Download XLSX • 80KB