79% OFF online library of tutorials for mastering Google Sheets

One time purchase $149

This tutorial is not available for free users. Please purchase a subscription to view this tutorial.

Hey there stranger!

Please sign-up or sign-in to watch this tutorial.

Find Keywords in Any Column, And create a quick search dropdown to find them.

About this Tutorial

Create a quick search dropdown to find keywords in any column.

Video Transcript

 Hey, so you might have this problem of finding keyword in columns. Now this might occur because you have some id and then you have a bunch of data, but like your data's like maybe words or some kind of like groups of words maybe. You know, you even have like a tag for in this column maybe. All of these things, right?

Maybe column comma delineated, or for a variety of reasons, you might have maybe nothing here, but this one is, you know that one and you wanna know, okay, I wanna know every one of these that has orange in it. There's only a couple, but I wanna know which one has orange. Well, It's command F you know, we can do this, right?

We can search and, oh, we found a few, right? But what if we want a list of them somewhere? So let's create a find. And we're going to add a row, a column actually, and we're gonna write all. Now you can do this on the same sheet, but sometimes you wanna do this sort of in , I don't know what to call it.

You don't wanna show it for some reason. So I'm gonna just have two, two columns in this sheet. And I'm gonna in one write ID and off. And here I'm gonna get an array. I'm gonna get an array formula of this. Okay? So that matches. Actually move that up. Just delete that. There's gonna be, oh, there we go.

I'm just gonna delete all of that. Delete. Okay. So we have all the IDs. Now we need everything that's in all of them. So I'm gonna do join, I'm gonna use join. I'm gonna delineate them by some thing, right? So this may be pipe, you can add spaces if you want, but I'm not going to. But what are the values?

The values are going to be db and now we can do this. We can just take all of that and see all of that is joined in one. Let me do that. See, everything is joined. Maybe we want it actually com delineated. There you go. Everything is is delineated by a comma. And yeah, we can copy paste this down, but we really want to.

Is we want to put, make this an entire array and then I think we can do I'm just hoping and praying

we can't do it. Ah, see join has to be a single row or a single column. So Array formula doesn't work for joint. Fine. That's totally. , we are going to just copy and paste this. We can do that. Oh, come on. Let's do that. Okay. We can just copy command C, shift command down, arrow sh, command B, and then we'll have everything.

So now, now in one row column. In one column we have everything. Okay. So, and we have the id. So all of.  matches, right? So we have this, and again, you can put this absolutely in just a column here. Say, Hey, join all that stuff together. And now find in the find sort of projecting. What I'm gonna do, we're gonna find and see what this is, is search for some thing.

So I'm gonna just click there. Query text to search is going to be actually, There's a little weirder way to do this. We can insert, right? And do find this is a fun way to do it. Maybe find and here find, what are we gonna find? We're gonna search for whatever we're looking for here. Let's call it c3.

We'll come back here, text to search, and we're gonna search here for it. Starting at one. . Yeah. One. And now go back here to C three. This is what we want to find. Straw berry. Okay. What did it find? Found at one. What did it find? Whoops.


There we go. So that, oh, I know why that messed up cuz We want C3 two B here. And this might be a funky way to do this, but it's also fun. I'm gonna copy and paste this all the way down. So now we'll have something. What happens if we have the wrong thing? What if it's like gray? Go back to all. And we have errors, right?

So if error, we're gonna just do nothing. And now we want to have a filter. So we're gonna do a filter. What are we gonna filter? We're gonna filter this all range, this entire range. We're gonna filter it by the condition that C is equal to or greater than zero. You might have some, oh, you might want to do just two and be two in the c2.

There we go. What's the na now? No matches found. N a no matches. There we go. So we can we might not want everything we actually might want only, Hey, and now what happens when we type in orange boom, we have all the IDs. So you can make this a nice, can pretty this up a little.  Fines and does it work?

Strawberry has all of them, right? So depending on how many you have in your database and how many keywords you have, you might be able to do something like take this then, then take this column, join smush it all into one place.  transpo. Split it again and transpose it, and then take the uniques. And so then you can make this, I'm not gonna do it right now, but then you can make this a dropdown menu.

But I think that would be, actually, that might not be too hard. What would that be? Equals join, we want to join it with, we want to join, oh, this.

and then this is easy. Transpose. And then split. What do we wanna split? What do we wanna split it by? Comma,

change this to true if we need to. There's everything. Oh, we have some spaces there. I think that's what we need. There we go. And then we need unique,

and those are all the options we have. We have some spaces here probably due to this. I'm going to guess I take those out. Let's go back to find. There we go. So those are all of our. And now we can change this, right Click to data validation list from a range and just list that. Save. Now we have a dropdown list of everything.

Let's go back to our db. If, you know, somebody si sets up blue, green whatever. Now go back to our find and they're all there. Blue, green, whatever, and we can find out exactly where they. , click whatever. This one, let's just double check that it's correct. Oh, there it is. Whatever. Hopefully this is fun finding keyword in a column.

We found the keywords, we put them all together, all the keyword we found, just a text. It's a really quick and fun way to create a little search function of tags or keywords or whatever you're looking for that's sort of separated through a bunch of columns. Bye.


Sheet Stories / Video Notes + ADDED: Email Notifications



Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet



Getting Started Coding in Apps Script

Seek Errors When Coding Apps Script

Think Like a Programmer: Develop The Mindset of an Apps Script Coder

ChatGPT Clone in Google Sheets Part 2

ChatGPT Clone in Google Sheets Part 1

Quickstart Tutorial OpenAI API in Google Sheets

Embed a Number in a Website from a Google Sheet

Create Navigation Like A Book or Presentation

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?


Embed a Headline in a Website from Google Sheets

Capture Emails from Website Form to a Google Sheet (Without Zapier)

Hold a Giveaway Raffle in a Google Sheet

Create a new Spreadsheet from just a Name in a Sheet.



Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets


Shwitter | Twitter in a Google Sheet

Create an Email Campaign Stats Calculator


Dylan Asks: How to Automatically Delete Rows If Cell Contains Value

Highlight Row as You Move Your Cell Selection

Create a Timer with Apps Script

LinkTree in a Google Sheet


Password Protecting Data In a Google Sheet

Automatic Weekly Backup of Google Sheets

Create a CPM Custom Function (Create Better Calculators!)

Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script


What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users


Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.


Sync Two Tabs Without ImportRange()

Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case



Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode


Write Your First Script


Find Keywords in Any Column, And create a quick search dropdown to find them.


Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator


Let's Make a Bookmarklet!


Troubleshooting Bitly in a Google Sheet Script


Unique Features - Design a Better Dashboard Part 2



How To Set Up Stripe Webhook to Google Sheets with Google Script


How to Edit a Macro


Sheet Stories / Video Notes + Clear 24 Hour Old Videos


Add A Timestamp to Task Lists (without Now Formula)


Make your Custom Functions Like Native Functions | Custom Function Autocomplete



Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop



New Syntax for WhatChanged Formula in Google Script


How to Record Macros


2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)


Deep Inside Dark Habits Google Script


How to Trigger Macros Daily


5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Script