How to Create a Simple Multi-Column Search Box in Google Sheets

A multi-column search box means a search box that can handle conditions in multiple columns. So such search boxes will definitely contain multiple search fields.

With the function Filter and Search, I am going to create a multi-column search box in Google Sheets. With this search box, you can search a table and filter the rows that contain the values you provide in the search fields.

Earlier I have shared an advanced single column search box.

I have used Query and some complex Query string comparison operators in that – How to Create a Search Box Using Query in Google Sheets. Personally, I like the Query search box.

In my real-life use, I have set up a search box using Query for a single column data that contains my 1500 (approx) post titles.

I find the search box very useful to search my said single column data in different ways. So that I can make sure that I am not repeating a title in my future posts.

But many users are looking for how to create a simple multi-column search box in Google Spreadsheets. I hope this tutorial will be useful to them.

How to Search a Table Using a Simple Multi-Column Search Box

First I’ll show you how the multi-column search box works in Google Docs Sheets. Then we can move to the tutorial section that details about creating it.

The below example demonstrates how a two-column search box works.

Multi-Column Search Box in Google Sheets

In this example, the search fields are in cell G2 and H2 which contains criteria for columns B and C respectively.

First I’ll demonstrate to you how the search field works. For that, I am going to use the Filter + Search combo in a single column.

Yep! Let’s begin with a single column search box.

Once learned you can create a multiple-column search box in Google Sheets hassle-free.

How to Use Filter and Search Functions to Search a Column

To filter a column or multiple columns, we can use the Filter function in Google Docs Sheets. There is Query too. But I am using the Filter function for the search box as it’s simple to learn.

Must Check: Google Sheets Functions Guide.

Then why am I using the Search function together?

To understand why I am using the Filter + Search function combo to create a search box, see the example below.

In the above example, my search field is cell C2 and the search key is “Fancy”.

Since the search key “Fancy” is not available, I mean as an exact match, the filter function in cell D2 returns the #N/A Error. Here is that formula.

Filter Formula:

=filter(A2:A,A2:A=C2)

In this the first argument A2:A is the data range, which means the data to be filtered.

The filter to be based on a condition. That condition is the second argument, i.e., A2:A=C2.

For partial match in Filter we can use the Search function as below.

Filter + Search Formula:

=filter(A2:A,search(C2,A2:A))

Results (Apple grades that contain the string “Fancy”)

U.S. Extra Fancy U.S. Extra Fancy U.S. Extra Fancy U.S. Fancy

It would return all the rows wherever the string “Fancy” appears in Column A. So it’s up to you whether you want a partial match or exact match in the search box to consider.

Note: Here with the terms the exact match, I didn’t mean case sensitivity. I simply mean a full string match or partial match.

I am following the Filter + Search method in my two-column search box below (see the animated screenshot at the beginning of this post).

How to Create a Simple Two-Column Search Box in Google Docs Sheets

I have a four column dataset in the range A1:D and the two search fields are in the range G2:H2.

The Filter + Search Combo Formula in cell F5:

=filter(A2:D,search(G2,B2:B),search(H2,C2:C))

Steps to create a two column Search Box in Google Sheets

This is a perfect example to how to create a multi-column search box in Google Sheets.

If you are going for an ‘exact’ match, then use the Filter formula as below. No need to use the Search function.

=filter(A2:D,B2:B=G2,C2:C=H2)

You can add more columns, if you have more columns to filter.

Additional Notes

In search fields, i.e in the cells G2 and H2, you may use data validation to create a drop-box to select the criterion. So that you can either choose a criteria/condition from the drop-down to filter or tap the delete button to make the criterion blank.

A criterion that doesn’t appear in the search field may break-your filter. The drop-box helps us to avoid that.

You can make the above search box more advanced if you know the usage of Filter function well.

I mean you can filter based on multiple conditions in single columns, use comparison operators etc. The below Filter tutorials will be helpful for you in this direction.

I have given enough examples to help you create a multi-column search box in Google Sheets that may useful to meet your needs.

If you find the tips useful, take a moment to share this tutorial. See the social Share buttons below.

15 COMMENTS

Kardha September 14, 2023 At 6:13 pm

Prashanth,

Thanks for the Search Box tutorial. I’m facing an issue when multiple users open the Google Sheet and enter their desired search text in the search box: the results are not shown.

The search works well when only one user is accessing the sheet, but not when more than one user is accessing the sheet. Is there a way to make the search capabilities work for multiple users at the same time?

Pete December 10, 2020 At 8:38 pm

This works great I have 2600 lines, columns a-n and the only issue is it only displays 10-16 search results, not 2600 if applicable.

- Prashanth December 11, 2020 At 6:20 am
  
  Hi, Pete,
  
  I don’t know what is the issue. Please see the answer to Dave. I’ve provided him a link to another tutorial related to a multi-column search.
  
  If that doesn’t help, if possible, give me access to your sheet. I’ll try to solve the problem.
  
Moni Musial November 21, 2020 At 1:11 am

Thank you for your help. I am trying to create a search bar for work and I have the formula working BUT I am not liking that when the box is empty where I am working I have all the results. Is there a way to get it so that the results only show up when there are works in the search box cell?

- Prashanth November 21, 2020 At 7:16 am
  
  Hi, Moni Musial,
  
  Thanks for your valuable feedback!
  
  Regarding your query, we can do that by using an IF statement that involves Counta.
  
  =if(counta(G1:H2),filter(A2:D,search(G2,B2:B),search(H2,C2:C)),)
  
  This will only work if any of the cells in the criteria fields G1:H2 have any values.
  
  The below formula will only work if there are conditions in G2:H2. Giving only the field labels in G1:H1 is not enough.
  
  =if(counta(G2:H2),filter(A2:D,search(G2,B2:B),search(H2,C2:C)),)
  
Dave September 4, 2020 At 6:28 pm

Hoping you still monitor this.

I have about 7 columns I want to do Filter and Search across, but it seems the more search boxes I add, the more problems I get with the results, in that a number of results are missing.

Does filter + search have known limitations like this, or if several search boxes are empty, it breaks down?

- Prashanth September 5, 2020 At 9:31 am
  
  Hi, Dave,
  
  I’ve once again gone through the tutorial.
  
  I haven’t found any issue in the formula. But I have noticed one thing, i.e. you can leave any criteria field blank. But entering a criterion that’s not available in the corresponding column makes the formula breaks!
  
  To avoid that use the data validation drop-box in the search fields. So you can select a valid criterion.
  
  If you still have issues with the formula, please leave the URL of your sheet in the reply (which I won’t publish). So that I can have a look.
  
  In addition to the above, there is one more multi-column search box tutorial – Filter Rows if Search Key Present in Any Cell in that Rows in Google Sheets.
  
TYSON November 13, 2019 At 12:49 pm

Keep up the good work Prashanth and thank you. I have adapted formula for 10 columns and made my life so much easier.

Amazing how little tricks make a massive difference.

Cheers

- Tammy November 14, 2019 At 2:14 pm
  
  How did you do that? When I more than 3 “search functions” within the filter(), I get an error that says:
  
  ERROR: FILTER RANGE must be a single row or column
  
Matt Larrubia October 24, 2019 At 10:49 pm

Hi,

I currently created a google form that has the following fields: User, Period, lab, URL.

I want to create a function that will allow me to filter into a NEW spreadsheet that holds all the users’ labs. So essentially I can view all the Labs from a User dynamically as they fill out the form.

So if the user submits the form with a new lab it will add another column to their row. Can you suggest any articles/videos help me accomplish this?

Thank you in advance.

- Prashanth October 25, 2019 At 1:56 pm
  
  Hi, Matt,
  
  Can you share the screenshot/or Sheet itself of your user-submitted data (sample) and the result you want?
  
  Best,
  
Josh June 3, 2019 At 6:21 am

Hi,

I’m trying to use the filter function but with a universal lookup value.

Example: If A1 contains the lookup value then something like the following:

=filter(C:C,D:D=$A$1,)OR(filter(C:C,E:E=$A$1,)

At the moment I have the lookup value for different columns in different cells running an ‘if’ function. Check A1, if blank check A2, if blank check A3, etc.

Any ideas on this?
Thanks in advance!
Josh

- Prashanth June 3, 2019 At 8:17 am
  
  Hi, Josh,
  
  I wish to see a demo sheet with the above problem to give an answer.
  
PETIT C January 19, 2019 At 6:25 pm

Hi,

Does it work with a regex formula if you wish to find a single word in multiple columns?

- Prashanth January 20, 2019 At 12:31 pm
  
  Hi,
  
  Please provide more details.
  
  Best,