Search Box in Excel (With Filtering and Dynamic Search Box)

In this Excel tutorial, you will learn to create a search box in Excel using several functions like FILTER, ISNUMBER, SEARCH, INDEX, and so on. Perhaps, The Conditional Formatting tool also plays a vital role.

We have used Microsoft Office 365 while developing the content. Although the FILTER function is only available in Excel 2021 and Office 365, the rest of the functions are applicable in older Excel versions from Excel 2007.

These days, there is extensive use of data. While working with these datasets, finding out a particular data randomly is not feasible for a professional. In this case, we need to apply an automatic process, therefore we don’t have to spend that much time and energy searching for information. Thus search box in Excel is irreplaceable.

Download Practice Workbook

In How Many Ways We Can Create a Search Box in Excel?

You can get specific data by following 3 convenient approaches. They are as follows:

1. Using Conditional Formatting tool.
2. Applying the FILTER function.
3. Joining IFERROR and VLOOKUP functions.

1. Can Conditional Formatting Highlight Searched Data?

Yes, you will be able to highlight necessary data based on logic from a dataset by using the Conditional Formatting tool. Perhaps, users can highlight necessary data by using suitable formulas and within the Conditional Formatting tool.

1.1 Using SEARCH Function in Conditional Formatting

Users can highlight necessary data by setting a search box and using the SEARCH function in the Conditional Formatting tool.

• Initially, go to the Home tab >> Expand the Conditional Formatting command >> Click on the New Rule option.

• Then, select the Use a formula to determine which cells to format option from the Select a Rule Type field.
• Next, insert the following formula containing the SEARCH function >> Click on the Format button:

• From the Formula, $E$15 = UK = Look up value and $A5&$B5&$C5&$D5 is the range A5:D5 where to search for matches and highlight.

• After that, select the background color >> Press the OK button.

• Therefore, we obtain the gold-marked data containing UK in the B5:E13 range after inputting UK in the E15 cell.

1.2 Inserting ISNUMBER Function in Conditional Formatting

Using another formula containing the ISNUMBER function in the Conditional Formatting, we can figure out the required data.

• Select the Use a formula to determine which cells to format option from the Select a Rule Type field of the Edit Formatting dialog.
• Then, insert the following formula with a combination of ISNUMBER and SEARCH functions >> Click on the OK button:

From the formula, E15 = USA matches with the B5 and the rest of the cells accordingly. If it figures out then highlight it with gold color.

• Finally, we get the gold-highlighted data containing USA in the B5:E13 range after typing USA in the E15 cell.

2. How to Use the Excel FILTER Function to Create a Search Box?

We can use the FILTER function to develop a search box and get results accordingly. The FILTER function takes data range and criteria as argument. On the other hand, it returns a range containing outcomes.

• Applying the formula as follows containing the FILTER function in the B18 cell, we obtain 2 data in the B18:E19 range for the USA:

• From the formula, E15 = USA matches with the B5:B13 range and picks data from the  B5:E13 = Array range.

Note: The FILTER function is only available on Microsoft 365, Excel 2021 and 2019 versions.

3. What Is Search Box Formula Merging IFERROR and VLOOKUP Functions?

You can combine the IFERROR and VLOOKUP functions that play a vital role in figuring out the required data from a dataset. This formula is a little bit complex since we will apply additional functions like ROW, IFERROR, SEARCH, RANK, and so on to fulfill our purpose. Please follow the steps carefully.

• Insert the following function containing IFERROR, SEARCH, and ROW functions in the E5 cell to assign a numerical value:

• From the formula, from the E5 cell, ROW() = 5 thus ROW()/100000 = 0.00005 and SEARCH($I$15,G5) = 1.
• Therefore, IFERROR(SEARCH($I$15,G5)+ROW()/100000,””) = 1.00005 since the IFERROR function prevents errors.

• Apply the formula in the D5 cell having the combination of RANK and IFERROR functions to make an ascending order based on the E5:E13 range:

• Here, the RANK function organizes data in ascending order. E5 = 1.00005 is the lowest in the E5:E13 range therefore it outputs 1 in the D5 cell.

• Now make a sequence inputting formula of the SEQUENCE function below in the B5 cell:

• Apply the formula with VLOOKUP and IFERROR functions to call up the names of representatives in the C5:C13 range:

• From the formula, the VLOOKUP function looks for the B5 from the D5:G13 range. 4 means the 4th column = Column G of the D5:G13 range and FALSE for the exact match.

• We obtain the information of representative Jason in the 10th row once we type Jason in the I15 cell.

How Many Ways to Create a Filtering Search Box in Excel?

We can obtain a filtering search box with the following approaches:

1. Searching within a single column.
2. Searching within multiple columns.

1. How to Search Within a Single Column?

In this case, we will search data from a single column, it is either Country or Representatives. To do so, we will use the Option button from the Developer tab to insert the Country and Representatives buttons as an available option.

• In the beginning, Select the B4:E13 range.
• Then, go to the Insert menu >> Click on the Table command.

• Then, rename the table name to avoid further confusion. Select the table >> go to the Table Design tab >> rename it as Table1 from the Table Name field.

• Next, go to the Developer tab >> click on the Insert command >> select the Option button command.
• After that, we inserted two buttons for Country and Representative.

• Further, select the Option button >> Right-click on the Mouse >> Click on the Format Control option from the context menu.

• Furthermore, in the Format Control dialog box, select Unchecked from the Value >> Insert 1 in the G2 cell >> Type G2 in the Cell Link field.

• Finally, inserting the following formula containing FILTER, ISNUMBER, SEARCH, and INDEX in the B5 cell, we obtain 3 data for the UK in the C2 cell:

2. How to Search Within Multiple Columns?

Using a complex formula including FILTER, ISNUMBER, SEARCH, and INDEX functions, we will be able to search among multiple columns. This method extracts data for partial matches too.

• Insert the formula as follows in the B7 cell:

• Therefore, we get 5 results regarding USA and UK once typing U in the E4 cell.

How to Make Dynamic Search Box in Excel

In this section, we will make a dynamic search box where the addition or reduction of data can take place. However, the process is a little bit complex since we insert additional columns, construct a drop-down list, and use different tools. Moreover, functions like UNIQUE, IF, INDEX, IFERROR, SMALL, and so on take place. Please follow the steps carefully.

Step-1: Make a Unique List

• To avoid repetition, convert the names of countries into a unique list by applying the following formula containing the UNIQUE function.

Step-2: Rename Range

• Select the G18:G23 range >> rename the range as country_list.

Step-3: Include Combo Box

• Go to the Developer tab >> Expand from the Insert command.
• Next, select the Combo Box command >> Place it to the existing worksheet.

Step-4: Get Context Menu

• Selecting the Combo Box >> Right-click on the Mouse >> Click on the Properties from the Context menu list.

Step-5: Re-Configure Properties

• Insert H15 in the LinkedCell field.
• Type country_list in the ListFillRange field.
• Input 2-fmMatchEntryNone in the MatchEntry field.
• Thus any selection from the drop-down list will be written in the H15 cell.

Step-6: Get Drop-Down List

• Ge the dropdown list from the Combobox by disabling the Design Mode.

Step-7: Insert Helper Column 1

• Inserting the formula containing ROWS in the F5 cell, we obtain a serial for the Helper1 column.

Step-8: Insert Helper Column 2

• Inputting the formula as follows containing a combination of IF, ISNUMBER, and SEARCH functions in the G5 cell, we obtain a serial for the Helper2 column.

Step-9: Insert Helper Column 3

• Applying the formula containing a combination of IFERROR, and SMALL functions in the H5 cell, we obtain a serial for the Helper3 column:

Step-10: Get Output

• We get 3 data for the UK that are selected from the drop-down list by using the formula as follows containing a combination of IFERROR, INDEX, and COLUMNS functions in the B16 cell:

Which Things to Remember When Creating Search Box in Excel?

• Application of SEARCH function within Conditional Formatting tool. Alternatively, a combination of the ISNUMBER and SEARCH functions also highlights the data.
• FILTER function to search data from.
• Combination of IFERROR and VLOOKUP to pick up a particular data. In this case, the RANK function provides ascending order.
• INDEX function to reference a cell where it is necessary. Besides, the IFERROR function to avoid error messages.
• Single-column filtered search allows gathering data from a particular column after selecting from the Option button.
• Multiple-column filtered search allows partial matches as well as broad matches.
• Dynamic search box requires multiple helper columns, a drop-down list, and numerous functions for functioning.

Frequently Asked Questions (FAQs)

Q1. Can we use the FIND function instead of the SEARCH function?

It depends on your preference since the FIND function is a case-sensitive function whereas the SEARCH function doesn’t respond to case sensitivity.

Q2. Where is the search button in Excel?

You can enable the search button by pressing Ctrl + F keys. However, to search for a text or number from the worksheet, go to the Home menu>> Expand the  Find & Select command >> Select Find option.

Q3. What is the search type in Excel?

The Excel SEARCH function is categorized under string or text functions, however, it delivers an integer output.

Conclusion

To summarize, the use of SEARCH and ISNUMBER functions within the Conditional Formatting tool highlights data. Additionally, we discuss the FILTER function and the combination of VLOOKUP and IFERROR functions to get necessary data. Further, we create a filtering search box with one or multiple columns using functions like FILTER, ISNUMBER, INDEX, and so on. Lastly, we construct a dynamic filter search box where we use different tools and functions such as, UNIQUE, INDEX, IF, SMALL, and so on. Finally, I believe this article will be beneficial to you in terms of creating a search box in Excel. Any suggestions as well as queries are appreciated. Hope to catch up soon with any new content.

Search Box in Excel: Knowledge Hub

• Create a Search Box
• Create a Search Box without VBA
• Create a Filtering Search Box for Your Excel Data
• Create a Search Box for Multiple Sheets
• Create a Search Box with VBA
• Create Search Box in Excel with Conditional Formatting

<< Go Back to Data Management in Excel | Learn Excel