HOW TO CREATE A SEARCH BAR - Google Sheets
Today I’ll show you how to create a dynamic search bar that can find anything from multiple columns, regardless of their content, in Google Sheets! We’ll be using the FILTER, ISNUMBER and SEARCH functions to achieve this.
First, here you can see the raw data that we want to search through and only return rows that contain what we are searching for.
Let’s examine what happens if we only use the filter function… To use it, write equals filter and define the range you want to filter, and select the criteria you want to filter based on. The criteria is defined by creating a BOOLEAN statement that is TRUE for some of the data in that column. However, if we just select the first column and make it equal to what is in the search bar, the filter function will only return exact values making it a lot less useful.
This is where the SEARCH & ISNUMBER function combination comes into the picture. The search function finds the position of a specific character or combination of characters in a cell. In other words, by linking what is searched for to the search bar, a number will only be returned if the same character(s) are found in the cell.
Then we can enclose this SEARCH function in an ISNUMBER function to return a TRUE, if a number has been returned, and a FALSE statement, if not. This means that we can use the resulting value of the function combination as the criteria in our FILTER function, only the cells that contain the string typed into our search bar are returned.
Now we can enclose these 3 functions into one big function. Then, we’ll also chain multiple of these combined functions together using plus signs in between. This creates OR statements, meaning that if any of the statements found inside of them are true, the resulting row, where one or more of these statements are true show up. We simply copy paste the functions and just change what column the search function looks through until we have one ISNUMBER & SEARCH combination function for each column we want our search bar to look through.
What’s especially neat with this is that the search bar can look for both letters AND numbers. It can even find what games have been released in a certain month using text format to find it!
Finally, we can enclose the entire function in the following IF statement, to make the results area empty until we actually enter some values into our function.
Watch video HOW TO CREATE A SEARCH BAR - Google Sheets online without registration, duration hours minute second in high quality. This video was added by user Lucas Learns 16 August 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 949 once and liked it 12 people.