Search boxes are really useful when you need to search and extract parts of information in your dataset, especially when you’re working with large volumes of data with multiple rows and columns. The QUERY function is greatly used for manipulating, searching filtering and extracting data in a few clicks.
In this tutorial, we are going to learn how to create search boxes using QUERY function in Google Sheets and to apply filters and criteria dynamically to your search query.
The Way To Write Queries With the WHERE Clause.
To use the QUERY function, you would have to make queries that interpret what you’re searching for to the Google Sheets program. This can be difficult for users who have little to no experience in writing queries.
But no need to worry, we are going to show you how to use the QUERY function properly from the basics. The QUERY function takes the syntax below.
=QUERY(data, query_string, headers).
Where;
- Data represents the cell range containing the data you want to search for.
- Query_string represents the search key that is the word you want to look up in your data range.
- Headers are the number of header rows in your dataset. It is an optional parameter in the function’s syntax.
Now that we know what the QUERY function’s primary job is, where does the WHERE clause come in?
The WHERE clause helps the QUERY function return data matching the given criteria. It filters matching data with the query string.
The WHERE clause serves as a query language, where we can state whatever we are looking for in the worksheet. For instance, the mathematical statement, “Select * WHERE A ends with ‘O'”.
The statement means Select the numbers (given criteria) WHERE (data range in column) ends with O.
Operators Used To Query the Dataset.
There are multiple operators accepted by the WHERE clause. These operators help to specify what your query means. There are two types of operators.
- Comparison operators
- Complex operators.
These operators are the simple ones. Examples of Comparison operators are.
= (equal to), < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to), ≠(not equal to).
Complex operators are mostly used for searching text values in a worksheet. Examples of complex operators are.
- matches: This operator helps enable queries by working with regular expressions.
- contains: This complex operator returns all data that contain the query string or search key.
- start with: This operator extracts terms that match the starting character of the search key.
- ends with: This operator extracts terms that match the ending or last character of the search key.
- like: This operator extracts terms that have matching special characters and wildcards with the query string.
How To Create a Search Box Using QUERY Function in Google Sheets.
Using the sample worksheet below, we are going to create a search box. The dataset contains the Customer names and Items purchased.
To do this, you need to follow the steps below.
Step 1: Select an empty cell in the worksheet and tag it as your search box. You can add color to the cell so that you won’t forget the search box. In the sample, I’ve selected cell E2.
Step 2: Next, we would insert a drop-down list into the search box. Go to the toolbar and select Data.
Step 3: Select Data Validation.
Step 4: The Data Validation tab is opened. Under Criteria, select List of Items.
Step 5: In the second criteria textbox, list the operators needed for your search box. They must be in lowercase, without spaces and with a comma separating each operator. In this example, we are adding the complex operators.
Step 6: Click Save.
Step 7: In the search box, we have a drop-down list.
Step 8: Select an empty cell as your results area.
Step 9: Select your operator from the drop-down list in cell E2 and enter a search key or criteria in cell F2. In this case, we have selected ‘start with’ as the operator and ‘A’ as the search key.
Step 10: Enter the QUERY formula which is
=QUERY(A2:B20, “SELECT * WHERE A “&D2&”, ‘”&E2&”‘).
Explanation of Formula.
- A2:B20 is the entire range of our data set.
- SELECT * WHERE is the start of the query formula. It must be placed in between quotation marks to avoid errors in the formula.
- A is the column. We are searching for some names in column A. If you’re looking for other information in the other columns, you can change it to B, C or D.
- &D2& is the cell reference where the operators are. We added dollar signs in between the cell reference to keep it locked in the formula.
- &E2& is where the criteria are entered. It is the search key in the formula. You add apostrophes and close parentheses.
Step 11: Press the Enter key and the results are automatically inputted in the result area.
The search box is very dynamic as you can select a different operator from the drop-down list and give new criteria. Press Enter and a different result is extracted into the results area.
Final Thoughts.
The QUERY function is very versatile. To harness its full power and capacity, check our full guide on the QUERY function. The search box tool is a very dynamic way of extracting and displaying important aspects of your data.
Now you know How To Create Search Boxes Using Query Function In Google Sheets. This feature would make work a whole lot better and easier for your analytics. I hope you found this guide helpful. Enjoy!
