A multi-column search box is an incredibly useful tool that allows you to handle conditions in multiple columns. With this type of search box, you can easily filter rows in a table based on the values you provide in the search fields. In this article, I will show you how to create a simple multi-column search box in Google Sheets using the Filter and Search functions.
How the Multi-Column Search Box Works
Before we dive into the tutorial, let’s take a look at how a multi-column search box operates in Google Sheets. In the example below, we have a two-column search box:
In this example, the search fields are located in cell G2 and H2, which correspond to columns B and C, respectively. The search box allows you to filter the table based on the criteria you enter in these fields.
How to Use the Filter and Search Functions for Column Searches
To filter one or multiple columns, we can utilize the Filter function in Google Sheets. Although there is also the Query function available, I prefer using the Filter function as it is simpler to learn. However, I often combine the Filter function with the Search function to create a more effective search box.
Let’s use an example to understand why combining the Filter and Search functions is beneficial. In the scenario below, I am searching for the term “Fancy” in column A:
Because there is no exact match for the search key “Fancy,” the Filter function in cell D2 returns the #N/A Error. To overcome this issue, we can use the Search function to allow for partial matches. The formula would look like this:
=filter(A2:A,search(C2,A2:A))This formula will return all the rows in column A that contain the string “Fancy.” You can decide whether you want the search box to perform partial or exact matches based on your needs. It’s important to note that when I refer to an “exact match,” I am not implying case sensitivity, but rather a full string match or partial match.
For our multi-column search box, we will follow the same Filter + Search method, using the animated screenshot at the beginning of this article as a reference.
Creating a Simple Two-Column Search Box in Google Sheets
To create a two-column search box in Google Sheets, follow these steps:
- Prepare your dataset in columns A to D.
- Place your search fields in cells G2 and H2.
- Use the Filter + Search combo formula in cell F5:
=filter(A2:D,search(G2,B2:B),search(H2,C2:C))Here is an illustration of the steps involved:
This example demonstrates how to create a basic multi-column search box in Google Sheets. If you prefer an “exact” match, you can use the Filter formula without the need for the Search function:
=filter(A2:D,B2:B=G2,C2:C=H2)Feel free to add more columns to the formula if you need to filter based on additional criteria.
Additional Tips
Here are a few additional suggestions to enhance your multi-column search box:
-
Use data validation in the search fields (cells G2 and H2) to create a drop-down menu where users can select criteria. This helps prevent errors caused by entering criteria that don’t exist in the search field.
-
If you are familiar with the Filter function, you can further customize your search box by filtering based on multiple conditions within a single column, using comparison operators, and more. Check out the Filter tutorials mentioned below for more advanced techniques:
I hope this tutorial has been helpful in showing you how to create a simple multi-column search box in Google Sheets. If you found these tips useful, please take a moment to share this article with others. Together, we can improve our spreadsheet skills and make our work more efficient.
