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

Get FREE Advanced Excel Exercises with Solutions!

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.

An overview image of search box in Excel

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.

getting the Conditional formatting tool.

  • 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:

=SEARCH($E$15, $A5&$B5&$C5&$D5)

  • 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.

Using SEARCH function in Conditional formatting.

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

selecting background color from the Format Cells dialog

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

highlight UK using Conditional Formatting tool

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.

Using ISNUMBER and SEARCH functions

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

Highlighting USA using Conditional Formatting tool

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.

Obtaining outcome using FILTER function

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.

Using the combination of IFERROR, SEARCH and ROW functions

  • 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.

Using the combination of IFERROR and RANK functions.


Using SEQUENCE function

  • 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.

Using the combination of IFERROR and VLOOKUP function

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

Getting the outcome.

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.

Converting into a table

  • 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.

Renaming the table

  • 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.

Inserting the option button

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

Getting 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.

Linking Option button to a cell

  • 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:

=FILTER(Table1,ISNUMBER(SEARCH($C$2,INDEX(Table1,0,$G$2))),"No Results!")

Using the combination of FILTER, ISNUMBER, SEARCH, and INDEX functions.

Formula Breakdown

  • INDEX function catches all the countries’ names from Table1.
  • SEARCH function figures out C2 = UK from Table1 Thus it returns 1 for each match.
  • ISNUMBER function converts the 1 into TRUE.
  • FILTER function gathers 3 data that output TRUE.

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:





FILTER(Table1,ISNUMBER(SEARCH($E$4,INDEX(Table1,0,5))),"No Results!")))))

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

Using the combination of FILTER, ISNUMBER, SEARCH, and INDEX functions.

Formula Breakdown

  • INDEX function catches all the country, representative, product, and Sales (USD) from Table1. INDEX(Table1,0,1)= range of country, INDEX(Table1,0,2)=range of Representatives, and so on.
  • SEARCH function figures out E4 = U from Table1 Thus it returns 1 for each match.
  • ISNUMBER function converts the 1 into TRUE.
  • FILTER function gathers 5 data that output TRUE.

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.


Using UNIQUE function

Step-2: Rename Range

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

Renaming the unique list range

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.

Inserting the combo box

Step-4: Get Context Menu

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

Getting the Properties option from the context menu

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.

reconfiguring the properties

Step-6: Get Drop-Down List

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

getting the drop-down list

Step-7: Insert Helper Column 1

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


Using ROWS function

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.


Using the combination of ISNUMBER, SEARCH, and IF functions.

Formula Breakdown

  • SEARCH($H$15,B5) outputs 1 for each match.
  • ISNUMBER(SEARCH($H$15,B5)) delivers TRUE for each 1 obtained by the SEARCH function.
  • IF function indicates to write the value of the Helper1 column for TRUE.

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:


Using the combination of IFERROR and SMALL functions.

Formula Breakdown

  • SMALL($G$5:$G$13,F5) outputs in ascending order. It finds F5 from the G5:G13 range.
  • IFERROR function writes the outcome from the SMALL otherwise, leave the cell blank.

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:


Using the combination of IFERROR, INDEX, and COLUMNS functions.

Formula Breakdown

  • COLUMNS($I$4:I4) = 1
  • INDEX($B$5:$E$13,$H5,COLUMNS($I$4:I4)) = INDEX($B$5:$E$13,2,1) that dictates to pick up the value of B6 cell which contains UK.
  • IFERROR writes the value obtained from the result of the INDEX function; otherwise, it leaves blank.

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.


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

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
MD Tanvir Rahman
MD Tanvir Rahman

MD Tanvir Rahman, BUET graduate in Naval Architecture and Marine Engineering, dedicated over a year to the ExcelDemy project. He is an Excel and VBA Content Developer. Having authored 50+ insightful articles, he actively updates and improves over 80 articles, reflecting his commitment to accuracy and currency, managing day-to-day operations, and analyzing and developing Excel and VBA tutorials. His broad interests encompass Data Analysis, Advanced Excel, VBA Macro, Excel Templates, Excel Automation, and Excel Power Query, showcasing a... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF