Skip to content

Create a Search Bar in Google Sheets

In this tutorial, you will learn how to build a search bar in Google Sheets. In other words, you will learn how to filter your data by entering a search word or phrase similar to a Google Search.

The Setup

Before you create the search function, you must first set up your spreadsheet. You will need three things.

  1. Your source data stored somewhere in your spreadsheet
  2. A designated cell to act as your search bar
  3. A designated space to display the results

Your source data can be anywhere in your spreadsheet.

Make sure you make it obvious where the user should type in the search terms.

Select the space where the results will be displayed. Also, to prevent unwanted errors, no data, text, or formulas should be placed in the same columns below the display area.

Once you have your spreadsheet set up in a similar fashion, you are ready to create the search formula.

The Search Formula

To create the search formula, you will be using three functions.

  • FILTER
  • ISNUMBER
  • SEARCH

Putting these functions together, your final formula will look like this.

=FILTER(sourceData_Range, ISNUMBER(SEARCH(searchBox, sourceData_ColumnToSearchIn)))

The SEARCH function looks for the search word or phrase within a specified column of the data range. It then returns an array of numbers and errors. The cells that contain the search term return a number, and those that do not return an error.

=FILTER(sourceData_Range, ISNUMBER({3, 34, error, error, 2, error, etc...}))

This array is passed to the ISNUMBER function. The ISNUMBER function transforms it into an array of TRUE and FALSE values. TRUE for each number, and FALSE for each error.

=FILTER(sourceData_Range, {TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, etc...})

Finally, the TRUE/FALSE array is used by the FILTER function to filter the data and return only the records that align with the TRUE values. In other words, the FILTER function returns only the records that include the search term.

As a result, you have a search bar that you can use to filter your data by any desired search word or phrase!

An Example

In this example, I have a list of blog posts as my source data. I have the date and description for each article.

The goal is to filter the blog articles by searching the descriptions for a specified search term. In this case, I want to filter the articles for those that contain Google Sheets within the description.

So I select the cell where I want the results to begin and enter the following formula.

=FILTER(E5:F18, ISNUMBER(SEARCH(C2, F5:F18)))

The SEARCH function looks for the text typed into cell C2 and searches for that text within the range of descriptions. Not the entire data range.

The SEARCH function generates an array of numbers and errors which is passed to the ISNUMBER function. Then the ISNUMBER function transforms the original array into an array of TRUE/FALSE values.

Finally, the FILTER function references the entire data range (including the dates) and uses the TRUE/FALSE array to filter the data. As a result, I get back only the blog articles that contain the search term within the description.

And that is how you can create a search bar in Google Sheets.

Download the Practice File

You can download the practice file here to give it a try yourself. After all, it’s best to learn by doing!

This Post Has One Comment

  1. To say that I’m impressed is a complete understatement. This is such creative formula building! I can’t wait to use this!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top