Google Sheets | Make a Dynamic Search Bar

Got Sheet is brought to you by beehiiv. If you’re considering starting a newsletter, check them out for 30 days free and 20% off your first 3 months…try beehiiv now.

MAIN ARTICLE
Query is our Friend

Sometimes using CTRL + F isn’t enough for our spreadsheet functionality.

Let’s build a simple, dynamic search bar to display all the results.

You can follow along in this walkthrough video:

Search Bar

We’ll start in J2 by merging some cells to make our Search Bar.

I'm searching the named range Transactions in columns A:F.

Then we want to search through (or QUERY) those transactions👇

A =QUERY() function in J5 will look at everything typed into J2 (the red circle) and display the search results below it (the blue rectangle)

Query Function

Query grabs all the info in either column B or column D that CONTAINS what we type in J2.

=QUERY(Transactions,
"SELECT A,B,C,D,E
WHERE LOWER(B) CONTAINS '"&J2&"'
OR LOWER(D) CONTAINS '"&J2&"'")

The LOWER command puts all our searchable columns into lowercase so that we don’t have to worry about capitalizing our searches in the bar.

* doing this does force you to use only lowercase in the search bar. Capital letters will mess it up.

Filter & XLOOKUP

We can achieve the same functionality with FILTER, though it is inferior. And even though it’s one of my favorite tools, XLOOKUP will not work in this scenario.

You can see more details in the video: https://youtu.be/QzqhVsXQfUc

Add an IF 😀

Because we don’t want an error to appear if we don’t type anything, I've added an =IF() function to handle the blank search bar.

Here’s the full code:

=IF(ISBLANK(J2),"", 
QUERY(Transactions,
"SELECT A,B,C,D,E WHERE LOWER(B) CONTAINS '"&J2&"'
OR LOWER(D) CONTAINS '"&J2&"'")

Got Sheet is brought to you by Coursera. If you learn well with structured courses, check them out for 30% off Coursera Plus…try them now.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.