To create a search box using QUERY in Google Sheets is useful to search for a piece of special information in your spreadsheet and extract the results in a fast and simple way.
Table of Contents
After you have set up such a search box in your sheet, it’s as easy to use as a search engine on the internet.
The purpose of creating a search box in Google Sheets isn’t just to search for a given word in your sheets. You have Ctrl+F to do that easily.
The real power of a custom search box lies in applying filters and special criteria when searching for information in the sheets.
So how do we do that?
QUERY function is a powerful tool to build real-life queries for many different applications. Check out our post where we shared the basics on how to use the QUERY function.
Now, let’s dive right into real examples to see how to create a search box using QUERY in Google Sheets.
The Way to Write Queries with the ‘WHERE’ Clause
The Language of QUERY
When using the
QUERY function, you should write the queries with the Google Visualization API Query Language, which is a special database-like language. This language is used to ask details about the data set.
If you don’t have any experience writing queries, they can appear a little complicated. However, with a bit of practice, you can learn the basics.
QUERY function takes at least two arguments. The first is the range of cells from where we query the data. The second argument contains the actual query written in the query language mentioned above.
The ‘WHERE’ Clause
Our main tool to create a search box using QUERY in Google Sheets is the ‘
WHERE’ clause. The ‘
WHERE’ clause is used to return only rows that match a specified condition, so it filters the data according to our requirements.
I like to describe the query language as something similar to the spoken language. For example, the expression below is written in the query language:
“ Select * Where A ends with ‘XYZ’ “
We can easily rewrite this expression into human language that makes perfect sense: select [the items] where [the content of column] A ends with [the term] XYZ.
So basically, after the ‘
WHERE’ clause we define what exactly we are looking for. Like in a search engine.
See how easy it is?
Operators Used to Query the Data Set
Now let’s see what exactly can be written into these queries that are useful in a search box. The ‘
WHERE’ clause in a query accepts several so-called operators.
The operators can be simple comparison operators:
- = (equal),
- < (smaller than),
- > (greater than),
- <= (smaller than or equal),
- >= (greater than or equal),
- != or <> (not equal).
You can even join multiple conditions using the logical operators and, or, and not.
WHERE‘ clause also supports some more complex comparison operators for text values, which are really useful when searching in a large set of data.
Look at these complex operators to see what they do:
- contains: This operator returns all the rows that contain the search keyword. The keywords may be present in any part of the whole row, such as in the beginning, middle, or end.
- starts with: It’s self-explanatory. This operator returns all the rows that start with the specified search term.
- ends with: Similarly, returns the rows ending with the search term.
- matches: This is a special operator that works with regular expressions enabling even more dynamic queries.
- like: A text search that supports two wildcards: %, which matches zero or more characters of any kind, and _ (underscore), which matches any one character.
Be aware that these operators used for text values are case sensitive, meaning that lowercase and uppercase letters are considered different letters!
There are several more features of the Google Visualization API Query Language, and you can get awesome results by experimenting with them.
Now, let’s see how to exactly write the formula to create the search box using the queries and operators in a ‘
A Real Example of Creating a Search Box Using QUERY
Take a look at the example below to see how to create a search box using QUERY in Google Sheets.
The above image shows how such a search box works. Here we have a list of cities with their population and country name. Apart from the list, we created both a search box area and a result area.
When we want to search, we need to select the operator (contains, starts with, and so on) from a drop-down list, and then in the next cell, write the search keyword we want to look up.
In the above example, we searched for cities that have names containing ‘ak‘.
The formula used in cell E7 is as follows:
=QUERY(A2:C60, "SELECT * WHERE A "&E3&" '"&F3&"'")
It’s not so easy at first, but let’s break this down.
Here’s what this example does:
- Prior to writing the query, we created the search box area that has a drop-down list with the types of operators (in cell E3) and an empty box for writing our search term (in cell F3).
- Then, we wrote the
QUERYfunction in the cell E7, where we wanted to start showing our results.
- There are 60 cities in the list, and their data is located in the cell range of A2:C60. This is the first argument of
QUERY, since it should be the whole range of data in which we search.
- After that, we used the query language. As I mentioned, we need the ‘SELECT * WHERE‘ expression to start our query. The query part should always be put between double quotation marks (“).
- After the ‘
WHERE‘ clause, we put the letter of the column by which we want to search. We wrote column A here because we want to search for the city names.
- Then comes the creation of the query by references. First comes the name of the operator that is in cell E3. The way we put it in the query is a bit more complicated because we need to extract the text of that cell. Without further explanation here, note that we should put the cell references between “ (quotation marks) and & characters.
- When it comes to the cell reference of the search term (F3), we should put an additional pair of ‘ (apostrophes) as well, because as shown above, the syntax of the ‘
WHERE‘ clause needs apostrophes around the search term.
- Eventually, this
QUERYfunction translates the selected references. In the picture above, it executes the following expression: “SELECT * WHERE A contains ‘ak’ “
- It updates the results automatically whenever we change either the operator or the search term in the search box.
This search box searches only for the city names, but you can expand it to work the populations and country names as well.
Try it out by yourself. You may make a copy of the spreadsheet using the link I have attached below and try it for yourself:
How to Create a Search Box Using QUERY in Google Sheets
- Firstly, create the search box area. Click on the cell in which you would like to put your operators. For this guide, I will be selecting the cell E3.
- To create the drop-down list, click on Data > Data validation on the menu.
- In the Criteria section, select List of items. In the next cell, write the exact name of operators you want to include in your search box, without spaces, and separated by a comma. Here I added the operators contains,starts with,ends with,matches,like.
- Great! After clicking Save, you have the drop-down list. Now select the cell you want to write your search terms. I marked the cell F3 yellow in my example to remember which one is the selected cell.
- After that, you need to select an empty area where you want to show your results. Select the first cell of this area (for example, cell E7). You might want to also create a header for this part too, as shown in my picture.
- Start writing the
QUERYfunction in this cell by typing =QUERY( and add the first variable. This should be the whole area where your data is written. Highlight the area with your mouse to add it to the function. I highlighted A2:C60 because I have my data about the cities in this range.
- Afterwards, put a comma to separate the first variable and write the query part in the second variable. You should put the query between quotation marks.
- Now, start the query formula with ‘SELECT * WHERE‘, followed by the letter of the column by which you want to search. I put column A to search by the cities, but it would be possible to use B for searching by populations or C for searching by countries.
- The next step is to select the operator. For this reason, you need to reference your cell where the operators are. As shown above, the cell reference should be put between the characters “& and &”. I have my operators in the cell E3. Hence, I write “&E3&”.
- Put a space and write the reference for the search keyword. It’s similar to the previous step, just select the cell where you write your search terms. Additionally, put the whole thing between apostrophes as well. As a result, you should write something like this with your own cell reference: ‘ “&F3&” ‘
- Finally, hit your Enter key to close the
QUERYfunction. Though you will see an error at this point, don’t worry, it’s only because your search box is still empty.
- Try out your newly created search box! Select an operator from the drop-down list, write a search term, and hit Enter. There you have your search results in no time.
That’s it, awesome! You can now create a search box using
QUERY together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier. 🙂