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.


Download Practice Workbook

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


2 Suitable Ways to Create a Searchable Database in Excel

Let’s assume we have an Excel large worksheet that contains the information about several Students of Armani School and College. The name 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.

create a searchable database in excel


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.

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

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

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

📌 Step 2:

  • Then select cell B4 in the Database Next, go to,

Data → Data Tools → Data Validation

  • Then select the Setting tab in the Data Validation Next choose List in the Allow: field using the dropdown arrow.
  • Then enter the following formula in the Source
=Filter!$E$5#
  • After that, go to the Error Alert tab.

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

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

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

Read More: How to Use Database Functions in Excel (With Examples)


Similar Readings


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.

Run a VBA Code to Create a Searchable Database in Excel

  • Next select New in the Name Manager window.

  • Then change the Name to Dropdown_List in the New Name window.
  • After that enter the following formula in the Refers to the field. Then hit the 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,"?*"))

Run a VBA Code to Create a Searchable Database in Excel

📌 Step 2:

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

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

Run a VBA Code to Create a Searchable Database in Excel

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

Run a VBA Code to Create a Searchable Database in Excel

  • Now right-click on the ComboBox and select properties.

Run a VBA Code to Create a Searchable Database in Excel

  • After that, select the Alphabetic tab in the Properties 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.

Run a VBA Code to Create a Searchable Database in Excel

  • Finally, the searchable database will work like Google Search.

Run a VBA Code to Create a Searchable Database in Excel

Read More: How to Create a Simple Database in Excel VBA


Things to Remember

👉 You can pop up 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.


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo