In this tutorial, you will learn how to create a search box in Google Sheets.
How to Create a Search Box in Google Sheets
Quick Navigation
Search boxes can be a great way to filter through data within a spreadsheet. For example, you may have a large list of data, and you want to quickly find a specific value.
We can easily create a search box feature in our spreadsheets by using the QUERY function.
The QUERY function is a powerful function in Google Sheets that allows you to pull specific data from a spreadsheet based on specified criteria.
It comes with an easy-to-use database query language that will allow us to filter, sort, group, and aggregate data.
In this guide, we will show you how to create a search box in Google Sheets using the QUERY function
How to Set Up a Search Box in Google Sheets
Here’s our step-by-step guide on how to set up a search box in Google Sheets.
Step 1
First, we’ll need to set up the search box and results table.
In the example above, we want to look up contact details of employees under specific departments.
The user can type a search query in cell I1 and our QUERY function will populate the range H4:J9 with the relevant details.
Step 2
We’ll start by selecting the top-left-most cell in our results table.
Type the QUERY function and add the dataset as the first argument.
Step 3
We’ll then use the second argument to generate a query that filters by department based on the value the user inputs in cell I1.
We can use the following formula: =QUERY(A2:F17,”SELECT B,D,E where F ='”&I1&”’”)
We will use the column letters to specify what output we want and what columns to use for filtering.
In our example, we want to filter our results by column F or the department field.
Step 4
When we input a string in our search box, the results table will reveal entries with matching departments.
Step 5
Instead of looking for an exact match, we can also use the “contains” keyword to allow partial matches.
For example, in the table below, searching for “Development” will return employees who work under either the Business Development or Research and Development teams.
You can allow for partial matches using the following formula:
=QUERY(A2:F17,”SELECT B,D,E where F contains'”&I1&”‘”)
Summary
This guide should be everything you need to learn how to create a search box in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.