Search Every Function in Google Sheets

About this Tutorial

Built a useful tool to filter Google Sheet formulas based on keywords.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div> Hi, welcome. Uh, if you are looking for any function , if it exists, uh, and you Google it, you're going to somehow end up here a Google Sheet's function list. Uh, this is from Google. It gives you two options. You can filter with a few key words, like, um, let's do. If, if we know what our, where it is or, um, partial, we get some results. We can also say, let's, let's say we, uh, filter by date. Oh yeah, we wanna work with dates. Okay, here's all the date functions . Well, this is all well and. Um, but, uh, one of the main things I can't do is, uh, write some notes .

Um, and, uh, it's, it's, it's frustrating that this syntax is, is most of the time. Uh, I do wanna see the syntax and I wanna understand it outside of Google Sheets and it's really hard to. Like, I can't even make it in one line. Like it's all, it's always, um, a folded word wrapped. So, um, and all these descriptions are word wrapped as well. It, it fits nicely into this table , right? Um, but sometimes this isn't how I wanna read it and sometimes I wanna write some notes , especially because I'm making these videos. I am looking for a way to capital catalog these and.

Make sure that I have covered the ones that I use the most. Check them off the list. Um, you know, and sometimes if you are truly, really excited to learn about Google Sheets, you might actually spend some time reading these. There's 491, I know this because I put them all on a Google sheet. Um, that's 492 rows, so that's 491, um, functions . Now there is still a lot that I have not gone through, so I'm, I'm going through them and learning more and more just by reading down and reading what they do. Formats and number of Roman nus amazing. You go from one to I or two to I.

I, um, what's interesting too is I will be writing some notes . maybe even doing some kind of check boxes and, and generally looking for, you know, a way to catalog and categorize these myself because these cat, these types aren't really super helpful. They're helpful to an extent, but here's two things that I did is I wanted to replicate. Also this filtering of keywords and this, this narrowing this, um, fascinating. So I did that. Um, and I wanna share with you how I did that.

So, um, because it's really, uh, fun to be able to create a little search, uh, search bar inside of your Google Sheets if you ever have a lot of data and you have some text in a column that you wanna search through. So here's what I did. So to filter by, Um, I set this up. In the header . So I took down a couple rows, I did a dropdown , and this dropdown actually is super easy. The data validation is literally just the entire column of data A to a, so it says a two to a 4 92, uh, list from a range.

Why that's interesting is because I didn't have to do, uh, I didn't have to figure out which ones were unique . Um, I didn't have to take out the duplicates. It does it automatically. Um, typically whenever I'm doing some kind of like dropdown , I'll use a unique list and then it'll update automatically. But this one, um, I didn't have to do that because I can, they. The data validation itself will automatically take out the, uh, duplicates. So it only shows unique . It's really interesting. So it's one step.

So all I did is I click data validation list from a range and listed the entire range . Obviously, this only works when, you know.  when you know absolutely that the data is, is good. Like if you don't know if you have duplicates, you should still do some kind of unique function and, and mung it a little, figure it out a little. Then the, um, filter is a couple things. So I, I did an if error , so let's take out this if error, just to show you how simple this is. So all I'm doing. Is, uh, taking a filter of everything. A to D, so a two Co, uh, colon D and I'm looking for.

To match C2 here, two, the column A of data , and that's it. That's just a filter . And so now, so, so if I took the, if I took the if error off, if I have nothing here, it gives me an error . So instead I put an if error around it so you can see the if error . , it says error pick type. So it's just telling me, Hey, if there's an error, just go pick type, because I know that the only errors are going to be, I know there's, uh, only so things I can select here. So none of these are gonna throw an error.  and the only error I'm gonna have is if it's blink.

So if somebody accidentally did that, and so it just tells you again to pick a type. Alright, search by keyword. This is what, this is the fun one. I've done this a few times. And here's one more time to show you how to do this. So again, I, I put a. Search bar up here made this nice header where the, uh, the place I want you to enter the, uh, words is white and then everything else is a nice blue. Um, and nothing special about this. Just have people enter a, a word there. , but here's the fun part.

It's a query and it's actually a really simple query because I want all of the four rows. So my, my, it is literally query then data , A two to d just to get the whole range . And the query is, is very similar to a sql. Query s ql.

And what I'm doing is I'm selecting all, which is this wild card, this, um, asterisk is a wild card saying select all where d which is the column I want to search through contains. And now in the, the, the big thing here, this is like the most important part, I come out of the.  quotes, and I put an and, and then I wrap an and around c2, but I also put these single quotes around it. So you gotta en exit the, uh, the, the text here with a double quote and C2 and double quote again, and then end this with a single quote and then end with double. So that is the trickiest part.

If I didn't have the quotes, it is, uh, an error and it gives us this error . Unable to parse query string for function query parameter two, no column partial. So what it's saying is that it's, it doesn't know that this is a word. It thinks partial is. Something else. And so the way to fix that is you just wrap it with single quotes inside of the double quotes and that's it. So now we have this cool little search function where we can search for words, um, count. And we can get all of the results there. So I just redid the function here inside of a Google sheet. Enjoy bite. Oh wait.

If you want, oh my God, if you want to get this, I'm making it available to you. Um, so just go in the bottom of this video and I put, all I did is I put copy here. Oh, not that copy. And so now you can make a copy of this Google sheet yourself, and you can, you can see how it works. Um, on your own and, uh, use it too. You can write your own notes here, and I challenge you to extend this to notes so you can do this.

Um, if you, I'm gonna take a pause here, and if you don't, if you want to go try it yourself, go ahead, but I'll tell you how to add the notes to this column here. So if I wrote some notes , I'm just gonna write some, hello. Goodbye. See, this is notes , so if you wanna know, just stand by for like five more seconds and I'll show you how to add this notes column. All right. I've given you plenty of time. So it's, it's, it's really simple. Go to the formula and you go change the D to an E. When we say notes , let's go to which one. Do we have some notes on Array.

So go to array, and there's our notes on search by keyword. Go up to the filter or the query change, D to E. And now we just head it notes . Now let's see what we did, uh, array. And there's our notes . There we go. So we just made our, our, our, um, Google Sheets function list inside of a Google sheet. Much better because now we have our own notes . Thanks. Bye.</div>