How to Create a Searchable Database in Excel (2 Quick Tricks)

While working with large Microsoft Excel, sometimes we need to search for some data. Creating a searchable database in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn two quick and suitable ways to create a searchable database in Excel effectively with appropriate illustrations.

Watch Video – Create a Searchable Database in Excel

How to Create a Searchable Database in Excel: 2 Quick Tricks

Creating a database is often necessary and making a searchable one boosts your speed while working in Excel.

Let’s assume we have an Excel large worksheet that contains information about several Students of Armani School and College. The names of the Students, their Identification Numbers, and the CGPA obtained by the Students are given in Columns B, C, and D respectively. We can easily create a searchable database by using FILTER, ISNUMBER, and SEARCH functions. We also use a VBA code to create a searchable database. Here’s an overview of the dataset for today’s task.

1. Combine FILTER, ISNUMBER and SEARCH Functions to Create a Searchable Database in Excel

First, we will create a searchable database using FILTER, ISNUMBER, and SEARCH functions. Let’s follow the instructions below to create a searchable database!

📌 Step 1:

First of all, select cell E5 in the sheet named Filter.

After selecting cell E5, write down the following formulas in the selected cell. The formulas are,

=FILTER(B5:B16,ISNUMBER(SEARCH(Database!B4,B5:B16)),"Not Found")

The SEARCH function in the formula searches for a given value.
The ISNUMBER function returns True if the output of the SEARCH function is a number. Otherwise, it returns False.
The FILTER function filters data according to the given criteria.

Hence, simply press ENTER on your keyboard and you will get your desired output.

📌 Step 2:

Then select cell B4 in the worksheet named Database. Next, go to,

Data → Data Tools → Data Validation

Then select the Setting tab in the Data Validation dialog box. Next choose List in the Allow: field using the drop-down arrow.
Then enter the following formula in the Source typing box.

=Filter!$E$5#

After that, go to the Error Alert tab.

Now uncheck Show error alert after invalid data is entered. Then hit the OK option.

Finally, a searchable database has been created. Now type something (R) in cell B4. Then select the dropdown arrow visible at the lower right corner of the cell. After that, you will see all the relevant search results as shown in the following screenshot.

2. Run a VBA Code to Create a Searchable Database in Excel

Now, suppose you don’t want to select any dropdown arrow to see the relevant results. Rather you want to see the search results as shown in Google Search. Then follow the steps below.

📌 Step 1:

First, you need to follow the steps before Data → Data Tools → Data Validation only in the earlier method.
Then select cell E5 in the Filter. After that, select Formulas → Name Manager.

Next, select New in the Name Manager window.

Then change the Name to Searchable_Database in the New Name window.
After that enter the following formula in the Refers to the field. Then hit OK. The formula uses the INDEX and COUNTIF functions.

=Filter!$E$5:$E$5:INDEX(States!$E$5:$E$17,COUNTIF(States!$E$5:$E$17,"?*"))

📌 Step 2:

Now go to the Dropdown sheet. Then select Insert → Combo Box from the Developer tab.

Next, drag the mouse to properly resize the ComboBox as shown below.

After that, you will see a new ComboBox created as follows.

Now right-click on the ComboBox and select Properties.

After that, select the Alphabetic tab in the Properties window. Then make the following changes: AutoWordSelect >> False, Linked Cell >> B4, MatchEntry >> 2 – fnMatchEntryNone.

Now copy the following code.

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "Dropdown_List"
Me.ComboBox1.DropDown
End Sub

After that, double-click on the ComboBox. This will take you directly to a new module in the Microsoft VBA window. Then paste the copied code into the blank module as shown below. Then press F5 to run the code.

Finally, the searchable database will work like Google Search.

Things to Remember

👉 You can pop up the Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon

👉 You need to deselect the Design Mode in the Developer tab to be able to type in the ComboBox.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Create Searchable Database.xlsm

Conclusion

I hope all of the suitable methods mentioned above to create a searchable dataset with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

<< Go Back To Database in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!