A Better Search Bar

These stories are presented thanks to beehiiv, an all-in-one newsletter suite built by the early Morning Brew team.

Fully equipped with built-in growth and monetization tools, no code website and newsletter builder, and best-in-class analytics that actually move the needle.

The top newsletters in the world are built on beehiiv, and yours can be too. It's the most affordable option in the market, and you can try it for free — no credit card required.

Get started →

I’m redoing a handy spreadsheet from last year this week. A while back, I put out this video which got pretty popular and details how to put a search bar into a spreadsheet.

It pulls data in real time from another tab based on what you type into the search bar cell.

And it was great.

But then I figured out a better way to do it.

First time around was a little clunky using FILTER or QUERY, but not both. There were case sensitivity issues. And, it just felt like a patch rather than a solid solution.

Well, there is a better way.

Sheets and Excel have a built in function called SEARCH. And we can use this with FILTER and a couple other friends to create a similar dynamic search bar without any issues.

Demo

If you want to follow along in the demo sheet I made, here it is.

Here’s what it looks like in our sheet. We can enter a search term and have results dynamically filtered below.

Here’s what the function looks like, and we’ll walk through it below.

=if(ISBLANK(C3),"",SORT(filter(transactions,SEARCH(C3,INDIRECT(C2))),3,FALSE))

ISBLANK()

The first thing we can do is check whether or not our search bar is blank. If it is, we don’t want any results displayed, and if it’s not, then we’ll do our searching.

NESTING

We have several functions nested together next. Let’s start with SEARCH(C3, INDIRECT(C2)).

SEARCH()

This is searching for the term in C3 (the search term we typed) within the named range of the value in C2.

This is a little fancier than maybe you want to go, but I created a named range for different searchable columns. In the pic above, we’re searching the named range “Place” which corresponds to a column in another tab of our spreadsheet.

INDIRECT()

We have to use INDIRECT to access the range that the string “place” is referencing.

The SEARCH function is looking for our search term, “home”, in our named range, “place”.

FILTER()

This is all within a FILTER function: FILTER(transactions, SEARCH(C3,INDIRECT(C2)))

This is filtering all of the items in the “transactions” named range according to which ones the SEARCH function found. The “transactions” named range is the full data set, so every line which has the search term in the “place” column, will be returned.

SORT()

The last thing we’re doing is simply sorting those by the Amount column (column 3).

This turned out to be a better solution than using Query because SEARCH takes care of case sensitivity automatically. It doesn’t matter if we have uppercase, lowercase or both in our search bar.

Have you ever used SEARCH()? What do you think of this little nested hack?

Thank you so much!

It means a lot that you’ve read this, and I hope it’s informed and/or entertained you for a few moments today!

Would love to say hi. Here are the best places to find me:

• 📺️ Videos on YouTube

• 💾 Products on Gumroad

• ✉️ Connections on LinkedIn

Need More Help?

• Need a powerful automation tool for Google Sheets? Try Coefficient to automatically import data and sync with your business systems.

• Hire an expert to help complete your next Google Sheets, Apps Script or Google Workplace project.