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

Here’s an overview of implementing a search box via the FILTER function.

Download the Practice Workbook

How to Create a Search Box in Excel – 3 Methods

Method 1 – Conditional Formatting to Highlight Searched Data

Case 1.1 – Using the SEARCH Function in Conditional Formatting

• Go to the Home tab.
• Expand the Conditional Formatting command.
• Click on the New Rule option.

• Select the Use a formula to determine which cells to format option from the Select a Rule Type field.
• Insert the following formula in the formula bar.

$E$15 = UK = Lookup value and $A5&$B5&$C5&$D5 is the range A5:D5 where to search for matches and highlight.

• Click on the Format button.

• Select the background color.
• Press the OK button.

• Press OK again.
• Excel highlights the data containing UK in the B5:E13 range after inputting UK in the E15 cell.

Case 1.2 – Inserting the ISNUMBER Function in Conditional Formatting

• Select the Use a formula to determine which cells to format option from the Select a Rule Type field of the Edit Formatting dialog.
• Insert the following formula in the formula bar and click on OK:

E15 = USA matches with the B5 and the rest of the cells accordingly.

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

Method 2 – Use the Excel FILTER Function to Create a Search Box

• Put a search term in cell E15.
• Applying the following formula in B18:

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

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

Method 3 – Search Box Formula Using IFERROR and VLOOKUP Functions

• We moved the dataset four columns to the right (to F:I).
• Insert the following formula in the E5 cell to assign a numerical value:

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 following formula in the D5 cell:

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.

• Insert the following formula in the B5 cell:

• Apply the following formula in the C5:C13 range:

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 to Create a Filtering Search Box in Excel

Method 1 – Search Within a Single Column

• Select the B4:E13 range.
• Go to the Insert menu.
• Click on the Table command.

• Select the table and go to the Table Design tab, then rename it as Table1 in the Table Name field.

• Go to the Developer tab.
• Click on the Insert command.
• Select the Option button.
• We inserted two buttons for Country and Representative.

• Select the Option button and right-click on it.
• Click on the Format Control option from the context menu.

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

• Insert the following formula in the C2 cell:

Method 2 – Search Within Multiple Columns

• Insert the formula as follows in the B7 cell:

• We get 5 results for USA and UK after typing U in the E4 cell.

How to Make a Dynamic Search Box in Excel

• Convert the names of countries into a unique list by applying the following formula in G18:

• Select the G18:G23 range and rename it as country_list.

• Go to the Developer tab.
• Expand the Insert option.
• Select the Combo Box and place it into the worksheet.

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

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

• Get the dropdown list from the ComboBox by disabling the Design Mode.

• Inserting the formula below in the F5 cell obtains a serial for the Helper1 column.

• Insert the following formula in G5 to obtain a serial for the Helper2 column.

• Use the following formula in H5 for a serial for the Helper3 column:

• Insert the following formula in B16:

• Change the values of the drop-down and you’ll get different results.

Frequently Asked Questions (FAQs)

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.

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, and select Find.

What is the search type in Excel?

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

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 Learn Excel