How to Create a Search Box in Excel (4 Easy Methods)

While working with a huge set of data in Microsoft Excel, Search box is an important tool to find particular data. A search box makes an Excel Worksheet more dynamic and handier. In this article, we will learn how to create a Search Box in Excel in four simple and suitable ways. You will also find appropriate illustrations of the whole procedure. I hope this article will help to develop your Excel proficiency.

How to Create a Search Box in Excel: 4 Suitable Ways

Let’s take a dataset regarding the Bill Status of ABC Corporation. This dataset consists of four columns (B, C, D, & E) containing fields named Customer ID, Customer Name, Bill, and Status respectively. We have 10 rows of data in the worksheet. From our datasheet, we will use Conditional Formatting, FILTER function, ISNUMBER function, & IFERROR function to create a search box in Excel. Here I am going to show a total overview of these four methods.

1. Use Conditional Formatting to Create a Search Box in Excel

In this section, we will create a search box using conditional formatting. This is an easy procedure. We will apply conditional formatting to create a search box. Let’s take a look back at our dataset and follow the instructions below to create a search box in Excel.

Steps:

• Firstly, select a cell where you want to search for any data. For our dataset, I have selected the H2 cell.

• After that, select cells ranging from B5 to E14.

• Then, go to the Home tab on the toolbar & select Conditional Formatting.

• Under Conditional Formatting, you will find the New Rule option. Click on the New Rule option.

• After entering the new rule option, you will find this window. Select the option Use a formula to determine which cells to format. 
• After selecting this option, you can see the Formula Bar.

• Then, in this formula bar, Enter the formula given below:

• After that, go to the Format option.

• Then select a color to see the searched result in the dataset. Here I have selected the Yellow color. Press the OK button.

• After that, you will find this window and can see the preview of the formatting. Press OK. 

• Then, you will find the whole dataset in yellow color.

• Now, in cell H2, search for any data according to the datasheet and you will find the result row colored with yellow color.

2. Introduce FILTER Function to Create a Search Box in Excel

In this section, we are going to learn how to create a Search Box in Excel with the help of the FILTER Function. It is another easy way to create a Search Box in Excel. Here I am going to show the whole procedure with the necessary illustrations.

Steps:

• Here we have taken the previous dataset.

• Select a cell to make a search box. I have taken cell C16.
• Now make a result box from B18 to E19. In this result box, you will find the searched result.

• Then, in cell B19, enter the formula given below:

In this formula,

• B5:E14 is the range of values to be filtered.
• C5:C14=C16 is the criteria to be matched.
• “NO MATCH FOUND” is the value to be returned when no entries meet the criteria.

• After entering the formula, the search box is ready to work. Enter a name into the search box. You will see the whole result.

Read More: How to Create a Filtering Search Box for Your Excel Data

3. Create a Search Box Using the ISNUMBER Function

In this section, we will learn the method to create a search box by using the ISNUMBER function. It is also a Conditional formatting procedure. Here I will explain the steps to explain the whole process of creating a search box using the ISNUMBER Function with necessary illustrations.

Steps: 

• First, Select H2 cell to make a search box.

• After that, from the toolbox, select the Home tab.
• Then, select conditional formatting from the ribbon. You will find the New Rule option there. Click on it.

• After that, you will find this window. Select the option Use a formula to determine which cells to format. After selecting this option, you will find a blank formula bar below the options.

• Then, in the formula bar, write the formula given below:

• After that, click on the Format button.

• Then, a window will be opened just like below. From the fill tab, select any color. Here I have selected the Blue color. Then press OK.

• After pressing the OK button, you will find a window just like below. You can preview the selected color. Press OK.

• Hence, you will see the whole dataset with the same blue color after formatting.

• Now, search for a name in the search box. You will find the result filled with blue color.

4. Apply IFERROR Function to Create a Search Box in Excel

In this section, we are going to describe the last method to create a search box in Excel. Here we are going to apply the IFERROR function to create a search box in Excel. I think you will find the method easy and interesting. Let’s follow the whole procedure to create a Search box in Excel.

Steps:

• We have taken a dataset ranging from B4 to D14.

• Then, for our convenience, we moved our dataset from B4-D14 to F5-H14.
• After that, select cell C4 to make a search box.

• Then, select E6 cell. Here, enter the formula given below:

Here,

• $C$4 is the text which is going to be searched.
• F6 is the cell number to perform the search.
• After searching, the function returns a value.
• This value is added to the ROW number.
• Then the value is divided by 100000.
•  Hence, the IFERROR function returns the exact value if there is no error.
• “” is the argument meaning the IFERROR function will return nothing if there is no error.

• Hence, use the Fill Handle to enter the formula from E6 to E15.

• Moreover, select the D6 cell to enter the formula given below:

Here,

• E6 is a value for which we will find the rank.
• $E$6:$E$15 is a range of cells to be ranked.
• 1 is a number to specify the order. Here, 1 is for ascending order.
• Then the IFERROR function will work just like explained earlier.

• Hence, use the Fill Handle again to enter the formula from D6 to D15.

• After that, input numbers from B6 to B15 as from D6 to D15.

• Then, in the C6 cell, input the formula given below:

As you can see,

• B6 is the cell that you want to look up.
• $D$6:$F15 is the range of cells you want to look for.
• 3 is the column number in the range to return the containing value.
• FALSE indicates to return a match.
• Then the IFERROR function will work just like explained earlier.

• After that, use Fill Handle to input the formula to the whole column.

• At last, you can input data into the search box. You can see the result just like shown below.

Things to Remember

• You need to have Excel 365 on your computer to use the FILTER function. Otherwise, you cannot use the FILTER function to create a search box in Excel.
• The VLOOKUP Function only looks up from right to left.

Download Practice Workbook

Download the practice workbook to exercise with a given dataset.

Conclusion

I hope these four methods will help you to solve the problems occurring in creating a search box in Excel. I think you will find interest in these methods. If you have any kind of queries, feel free to ask me in the comment section.

Related Articles

• Create a Search Box in Excel with VBA
• How to Create a Search Box in Excel Without VBA
• How to Create a Search Box in Excel for Multiple Sheets

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