You can create your own custom search box in Google Sheets to extract rows that matching the search phrase. I am using one such custom search box and I find it very useful. So I thought I should share that Google Sheets tip with you. You can learn in this tutorial how to create a search box using Query in Google Sheets.
On the web, we use the search fields to find the required information quickly. In this blog also you can find a search field/button on the top menu bar that you can use to search for this blog content.
But in Google Sheets, we can use a custom search box that built with Query to search and extract related information quickly that present within the spreadsheet.
You may notice that some of the web search engines allow applying a filter to the search result. We can follow a similar approach in Google Sheets too.
My search box using Query in Google Sheets supports a few wildcards and even Regular expression matches in the search field. All that information you can find below. Also, I have included a link to my example sheet (contain all the formulas and settings) at the end.
The Purpose of Creating a Search Box Using Query in Sheets
I am using the said custom search box for the following purpose. But the ultimate goal is to filter a large set of data based on criteria and string comparisons.
I have created a Search Box using the Query function in Google Sheets for the following purpose.
For your info, I have 1000+articles on my blog Info Inspired and in that around 500 tutorials are related to Google Sheets. What I have done is copied the titles of all that Google Sheets tutorials to one of my Google Sheets files.
Why? The reason, I have multiple tutorials on the same topic on this blog. For example, if you take Vlookup as an example, I have around 25+ tutorials here on that topic.
I have already that title on my Sheet file. With the custom search box in that, that I have created using Query, I can search all the titles and extract the titles that contain the keyword “Vlookup”. So that when I write a new tutorial based on the topic Vlookup, I can avoid repetition.
There is one more benefit. Using my custom search box in Google Sheets, I can search a keyword in different ways.
I am making the best use of the Query string comparison operators in my custom Search Box that I have created using Query.
If you want to know how to create a Search Box using Query in Google Sheets and also get the formula just read on.
I will provide you the formula and the tips to use this custom search box in Google Sheets for your own purpose.
Instructions to Create a Query Powered Search Field in Google Sheets
Data validation is the first step to create the above Search Box that uses Google Sheets Query function.
Cell B1 is my search field and the formula is in cell B2. The search result will appear in the range B2:B.
To control the search output I have a drop-down menu in cell A2 that acts as a filter.
Let’s start configuring the search box.
Data Validation for Query Search Box
We must first set two data validation rules. One in cell B1 and the second one in cell A2. In that, cell B1 is our search field. So we must only allow small case letters in this cell to avoid case sensitivity. I am not going into those details. I will detail the steps involved.
Using data validation you can limit user entry in a cell into lower, proper or upper case. Here is that detailed tutorial page link – Force text entry to lower, upper or sentence case. You can check that later. Here just follow the below setting.
Note: My data to search is in Sheet1!A1:A. I am setting up the search field using Query in Sheet2.
Data Validation Drop-Down 1 for Search Field
The active cell is B1 (the search field) is in Sheet2. Now go to the menu Data > Data Validation. Copy and paste the below formula in the criteria field.
=EXACT(LOWER(B1),B1)
Data Validation Drop-Down 2 to Filter Data Using String Comparison Operators
Now see the configuration of the second data validation rule. In the cell A2 in Sheet2, create this drop-down using data validation.
In the criteria filed, enter the below string comparison operators. No doubt I will give you the relevant tutorial links below to help you learn in detail about the use of these complex comparison operators in Query.
contains,starts with,ends with,matches,like,<>This creates a drop-down menu containing the string comparison operators. You can select this later to dynamically find and extract the data based on the search keyword.
You are learning to create a Search Box using Query in Google Sheets and have already completed the major part of the Search Box configuration. Now the last part is the Query formula.
Use this Query formula in cell B2.
=iferror(query(Sheet1!A2:A,"Select A where lower(A) "&A2&" '"&B1&"'"),"Not found!")That’s all. Your custom search box in Google Sheets is ready. Type your search phrase in cell B1. Then choose either of the comparison operators in the cell A2.
The Role of Complex Operators in The Search Box Powered by Query
contains – If you choose this operator, for example, the search keyword “query” will return all the rows that contain the text ‘query’. See the detailed Google Sheets tutorial on this topic here – CONTAINS Substring Match in Google Sheets Query for Partial Match.
starts with – extract the rows that start with the search phrase. See the details – Starts with and Not Starts with Prefix Match in Query.
ends with – extract the rows that end with the search phrase. Want to learn more? Here you go – Ends with and Not Ends with Suffix Match in Query.
matches – This is a (preg) regular expression match. With this string comparison, you can dynamically control the search output. Find more details here – Matches Regular Expression Match in Google Sheets Query.
I hope, the below screenshot gives you a glimpse into the use of ‘matches’ in the custom search field in Google Sheets.
like – This operator enables us to use two wildcards in Query – How to Use LIKE String Operator in Google Sheets Query.
<> – When you choose this operator, the formula extracts the rows that are not equal to the search phrase. So use this operator wisely.
I guess you can now create and use a powerful Search Box using Query in Google Sheets.
Please understand that my search box only searches one single column in Sheet1 and extract the result in Sheet2. But you can tune the formula to extract more than one column – How to Create a Simple Multi-Column Search Box in Google Sheets.
Hi Prashanth,
I have a google sheet with all of the seeds I have for the next gardening season. I want to be able to search for a name and then have it display all of the information within that row.
So if I Vlookup up “Tomato”, A listing of each row where the title (column1) at least contains the word “Tomato” and what comes up is everything within that row (in each column) with header titles proceeding each new column. Ie:
Roma Tomato, Sunlight: Full Sun, Days to Maturity: 80 Days
Plum Tomato, Sunlight: Partial Sun, Days to Maturity: 45 Days
Thanks!
Hi, Ben M,
Here is an example.
Assume the table contains 3 columns (A1:C) and column A contains the name of seeds.
So, you can use the below Query.
=query(A1:C,"Select * where A contains 'Tomato'")In this, the criterion is “Tomato”. Enter this criterion (without double quotes) in cell E1. Then the formula would be;
=query(A1:C,"Select * where A contains '"&E1&"'")I’m using Google sheets to maintain a list of addresses that we’ve collected items from, where we limit them to 3 per year.
I’m new to Sheets so I’m not sure how to even begin. What formula would I use to search by house number, followed by Street name?
Hi, Donna Osmon,
Make a sheet that contains some sample addresses and share that sheet link via a reply to this comment.
I would be happy to assist.
What would be the simplest way of having no queried values displayed if nothing is entered into B1?
i.e. I do not want a list of thousands of values displayed when B1 is empty.
Hi, Frank,
You may please modify the formula as below.
=iferror(if(B1="",,query(Links!A2:A,"Select A where lower(A) "&A2&" '"&B1&"'")),"Not found!")This is just what I was looking for – thank you.
Is it possible to display the result in the cell below the formula so I can copy and paste it?
Hi, babop,
This will add one blank row on the top.
={"";iferror(if(len(B1),query(Links!B2:B,"Select B where lower(B) "&A1&" '"&lower(B1)&"'"),"Not found!"))}But actually adding one row on the top is not required to copy-paste the result.
It doesn’t matter whether there is a blank row on the top or not, you must copy and paste it as values (copy, right click to see “paste special”).
Hi there,
I have a phone book where each letter has its own sheet from a-z, how can I make this formula work so that when I want to find everyone living in a certain street I can search “contains the street name” and it shows all the names that live in that street? Thank you.
Hi, Ervin,
You may need to import the data from the sheets A-Z in a master sheet. Then you can use my formula.
To import data from the sheets A to Z to a master sheet, you can use the syntax as per the formula below.
=query({A!A1:B;B!A1:B;'C'!A1:B},"Select * where Col1 is not null")This formula will import data from Sheet A, B, and C to a master sheet. The columns included are columns A and B.
Once imported (compiled) the data, you can try the search box explained in this guide (multi-column search).
Filter Rows if Search Key Present in Any Cell in that Rows in Google Sheets.
Hi there,
How would I go about searching across multiple sheets?
Hi, Sarah Daly,
Include the sheets within the formula. Here I am including the sheets Sheet1, Sheet2, and Sheet3.
=iferror(query({'Sheet1'!A2:A;'Sheet2'!A2:A;'Sheet3'!A2:A},"Select Col1 where lower(Col1) "&A2&" '"&B1&"'"),"Not found!")Please note the changes.
How can I make this search box using Query work for a mixture of numbers and words?
For example, if one cell contained “222′ and the cell below that contained “Fun”.
At the moment, whenever a value is entered into the Links list on your example sheet the formula hits the error “Not Found” message.
Good question.
Use To_text with the data (range) and change Colum identifiers from A to Col1.
Formula:
=ArrayFormula(iferror(query(to_text(Links!A2:A),"Select Col1 where lower(Col1) "&A2&" '"&B1&"'"),"Not found!"))Here is a related post – How to Solve the Mixed Data Type Issue in Query in Google Sheets.
Hi, I followed the steps above. When I enter the formula in cell B2, all values are returned in B2 and cell B1 not working. Am I missing anything?
Hi, Ram,
Did you see my example Sheet linked within the post?
You can refer to that to sort out the error.
Best,
If you add into LOWER() into the B2 formula by using the formula:
=iferror(query(Sheet1!A2:A,"Select A where lower(A) "&A2&" '"&LOWER(B1)&"'"),"Not found!")Then you skip on needing any data validation within B1. People can enter anything and the search is not case sensitive.
Hello!
What if I want to hide my source file. Can I use Importrange? How do I make it work with this formula?
Thank you!
Hi, Nikki,
To hide your source file that contains the information to search, as you have said, you can use the Importrange.
See the changes in the formula below.
=iferror(query(importrange("Your File URL here","'your tab name'!A1:A"),"Select Col1 where lower(Col1) "&A2&" '"&B1&"'"),"Not found!")Note: First enter the Importrange formula alone and ‘Allow access’. Delete it. Then use the above formula.
Best,
Hello. What if you want to search something giving 2 columns? for example, select A, B where lower(A), lower(B) contains…
Hi, Gabriel Cerqueira,
Here is one example.
=Query(A1:B,"Select A,B where lower(A) contains 'apple'and lower(B) contains 'apple'")Best,