In this excel tutorial, we are going to learn how to make a Search box. After creating search box, we will use it to search and display data from the database. In the above video you can see what we are going to create in this tutorial. We are going to do this in two steps.
- Creating search box.
- Displaying search result.
Creating Search Box
In this section we are going to create search box and the display box. Instruction for creating search box and display box are given below.
- Select cells "C2:E2" and click on "Merge & Center".
- Select all the cell around the merged cells and fill it with black color.
- Click on "B2". Change font color to white, font size to 12 and make it bold then type "Search".
- Select cells B5 to F5, hold Ctrl and select C6:F6, C7:F7, C8:F8, C9:F9, C10:F10, C11:F11 and C12:F12 and click on "Merge & Center".
- Fill with color as shown in the picture above.
- Click "B5" hold Shift and click on F12.
- Click on "Thick Box Border" from the border options.
Displaying Search Result
Here is the sample data which I will be using in this tutorial to show you how you can search and data from the data base.
| Click Image to Enlarge |
- Go to Sheet 2 in the same workbook.
- Click on the formula tab and then "Define Names".
- Create the following named ranges i.e. Name, Age, Gender, Phone, Email, Address, and Country and type in the following formulas in "Refers to:" text box respectively
- "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Shee2!$A:$A)-1)"
- "=OFFSET(Sheet2!$B$2,0,0,COUNTA(Shee2!$B:$B)-1)"
- "=OFFSET(Sheet2!$C$2,0,0,COUNTA(Shee2!$C:$C)-1)"
- "=OFFSET(Sheet2!$D$2,0,0,COUNTA(Shee2!$D:$D)-1)"
- "=OFFSET(Sheet2!$E$2,0,0,COUNTA(Shee2!$E:$E)-1)"
- "=OFFSET(Sheet2!$F$2,0,0,COUNTA(Shee2!$F:$F)-1)"
- "=OFFSET(Sheet2!$G$2,0,0,COUNTA(Shee2!$G:$G)-1)"
Formula To Enable Search
- Go back to "Sheet1".
- Click "C6" and type "=IF($C$2="", "", $C$2)".
- Click "C7" and type "=IF($C$2="", "", INDEX(Age,MATCH($C$2,Name,0)))".
- Click "C8" and type "=IF($C$2="", "", INDEX(Gender,MATCH($C$2,Name,0)))".
- Click "C9" and type "=IF($C$2="", "", INDEX(Phone,MATCH($C$2,Name,0)))".
- Click "C10" and type "=IF($C$2="", "", INDEX(Email,MATCH($C$2,Name,0)))".
- Click "C11" and type "=IF($C$2="", "", INDEX(Address,MATCH($C$2,Name,0)))".
- Click "C12" and type "=IF($C$2="", "", INDEX(Country,MATCH($C$2,Name,0)))".
Now click on the search box and type a name you want to search. If the name you are searching is in the database, it will be displayed in the search "Search Result...". If the name you are searching is not in the database it will display "#N/A" in all the fields except name field. If you like this tutorial or if you have a query, leave a comment at the end of this tutorial. Happy searching.
