If you’re looking for an advanced formula to search for specific keywords in Google Sheets or Excel, setting it up is simpler than it seems. Unlike built-in search tools, the FILTER function allows you to create dynamic filters with specific criteria that can change over time.
Combining the FILTER function with other functions in Google Sheets enhances data filtering and analysis capabilities:
- FILTER + ISNUMBER + SEARCH: Filters rows containing specific keywords by checking if a keyword is found within a column.
- FILTER + ARRAYFORMULA: Applies conditions across entire data ranges, enabling more dynamic filtering.
- FILTER + SORT: Filters data and then sorts the results based on specified criteria.
- FILTER + UNIQUE: Filters unique rows that meet specific conditions.
- FILTER + INDEX + MATCH: Retrieves specific values from filtered data, enhancing lookup capabilities.
- FILTER + IF: Applies custom conditional filtering for complex data requirements.
- FILTER + AND/OR: Combines multiple conditions for more refined filtering.
The above are the combination of the FILTER function with other functions like ISNUMBER, SEARCH, ARRAYFORMULA, SORT, UNIQUE, INDEX, MATCH, IF, and AND/OR makes Google Sheets a powerful tool for customized data filtering and analysis. These combinations enable you to create complex, dynamic, and highly tailored filters, making it easier to perform data analysis, generate reports, and make informed decisions.
In this guide, we will explore how to use the FILTER function along with ISNUMBER and SEARCH to filter data based on specific keywords. This approach allows you to easily find and display rows related to your search criteria. For example, using the formula:
=FILTER(‘New-Bills’!B2:H,ISNUMBER(SEARCH($C$1,‘New-Bills’!B2:B)))
This formula filters data from the ‘New-Bills’ sheet, checking each row in column B to see if it contains the keyword entered in cell $C$1. If the keyword is found, the row is included in the results. This combination provides a more dynamic and precise way to filter and manage data based on specific keywords, making your data handling more efficient and effective.
FILTER Function:
- The FILTER formula is used to extract data that meets specific criteria from a range. In the above formula, it’s used to filter data from the range ‘New-Bills’!B2:H.
- This range specifies columns B to H, from row 3 onward, in the ‘New-Bills’ sheet. The function will return only those rows that meet the specified condition.
Syntax: =FILTER(range, condition1, [condition2])
Range is the dataset you want to filter. Condition1, Condition2, etc. must be of the same size as the range. The FILTER function does not allow mixed conditions of different sizes. If the conditions don’t match the size of the range, the function will result in an error.
ISNUMBER Function:
- The ISNUMBER formula checks whether the result of the SEARCH function is a number (i.e., whether the keyword was found).
- If the keyword is found, ISNUMBER returns TRUE, allowing the FILTER formula to include that row in the results, otherwise its returned as FALSE or ERROR.
Syntax: =ISNUMBER(value)
SEARCH Function:
- The SEARCH formula looks for the specified keyword in a text string and returns the position of the first character of the keyword within the string.
- If the keyword is found, it returns a number; if not, it returns an error. In our example, it searches for the keyword specified in cell $C$1 within column B of the sheet ‘New-Bills’.
Syntax: =SEARCH(search_for, text_to_search, [starting_at])
Combining the Functions:
- The condition ISNUMBER(SEARCH($C$1,’New-Bills’!B2:B)) inside the FILTER formula checks each row in column B to see if it contains the keyword entered in $C$1.
- If the condition is TRUE for a row, FILTER includes that row in the results.
=FILTER(‘New-Bills’!B2:H,ISNUMBER(SEARCH($C$1,‘New-Bills’!B2:B)))
How to Use the Advanced Function for Search Keywords:
First you need to go to a new blank sheet (here we named our sheet by “Search”), setup your header row as below if you need the same or as your reference order. Here we setup the header row in Cell A2:H2 in the named sheet “Search”.
| B3 | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Search–> | enter here search keywords | ||||||
| 2 | Sr.no. | Supplier | Bill Type | Bill no. | Amount per unit | Qnty | Total Cost | Bonus |
| 3 | 1 | =FILTER(‘ | <– put the formula here |
Next you need to go to the cell $C$1 where your search keyword need to be put in the cell. Type the formula in cell B3 by starting with an equal sigh ( = ) followed by the formula as shown below:
=FILTER(‘New-Bills’!B2:H,ISNUMBER(SEARCH($C$1,‘New-Bills’!B2:B)))
Note: the range and the sheet name is depend on your selection or preference please.
The formula will automatically filter and display all rows from ‘New-Bills’!2:H where the keyword from $C$1 is found within column range B2:B.
If you look at the image above, the FILTER function efficiently searches through the data to find all instances where the keyword “Tata” is present in the database sheet named “New-Bills” at column B. This approach is highly effective for sifting through large datasets quickly, allowing you to pinpoint specific information with ease, thereby enhancing your data management and organization. It is especially useful when searching for a particular name or keyword that occurs multiple times within your dataset, as it filters and displays all relevant rows associated with the search term immediately.
- How to Separate Text and Letters in Google Sheets using REGEXREPLACE
- Income Tax Calculation in Google Sheets for Salary Employees for FY 2024-25 (AY 2025-26)
For example, if you need to find all entries related to a particular employee or supplier in the “New-Bills” sheet, you can simply enter the supplier/employee’s name or keyword into cell $C$1. The formula will then filter and display all rows containing the search term, providing immediate results for your query. This approach streamlines the process of locating repeated entries and enhances the overall efficiency of managing large datasets.
Overall, this formula filters and displays all rows from the range ‘New-Bills’!B2:H where the keyword in $C$1 is found within column B. It’s particularly useful for quickly locating rows that match specific search criteria within large datasets, allowing for efficient and targeted data retrieval.
TEXTJOIN Function Make More Dynamic Search Result
The TEXTJOIN function in Google Sheets can make search results more dynamic by allowing you to combine multiple text strings from different cells or ranges into a single cell with a specified delimiter. This function is particularly useful when used alongside other functions like FILTER, SEARCH, and ARRAYFORMULA to create more complex and flexible search results.
Here you just need to add the TEXTJOIN function in column I of the sheet range for the FILTER function “New-Bills”. Its combines the results into a single, comma-separated string, which the FILTER extracts the rows containing keyword $C$1 in the column I2:I already join by the TEXTJOIN function the whole data in single column, providing a compact and readable output of all matching entries. This approach makes it easier to review and analyze search results directly within the cell.
Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])
This formula combines the text from cells B2 to H2 into a single, comma-separated string in column I of the “New-Bills” sheet.
Here’s how TEXTJOIN enhances search results:
=TEXTJOIN(", ",1,B2:H2)
And now you need update in your previous FILTER function given below:
Previous the FILTER Function:
=FILTER('New-Bills'!B2:H,ISNUMBER(SEARCH($C$1,'New-Bills'!B2:B)))
The previous version of the formula only searches for the keyword in column B, limiting the search to that single column. As a result, if you want to search for other criteria like Bill Types, Bill No., Amount, Quantity, Total Cost, or Bonus, the function won’t work, and it will return no results.
This constraint makes it difficult to filter data comprehensively across multiple fields, which is why updating the formula to include a broader range and using functions like TEXTJOIN can significantly enhance its capability to find and display relevant information from multiple columns.
Updated FILTER function:
In the updated version, the range is changed from ‘New-Bills’!B2:H to ‘New-Bills’!B2:I, and the search column is updated from ‘New-Bills’!B2:B to ‘New-Bills’!I2:I.
This change allows the FILTER function to search within the entire concatenated data in column I, thanks to the TEXTJOIN function, providing a dynamic search result across the entire range rather than just a single column. This makes it much easier to review and analyze all relevant data entries related to your search keyword in cell $C$1.
=FILTER('New-Bills'!B2:I,ISNUMBER(SEARCH($C$1,'New-Bills'!I2:I)))
In the above image, you can see an additional column “I,” which displays the results of the `TEXTJOIN` function applied in the “New-Bills” sheet. If you don’t want this extra column to be visible, here are a few solutions:
- Hide Column I: A simple solution is to hide column “I” to avoid showing the merged data while keeping it functional.
- Apply White Font Color: Another option is to set the font color of column “I” to white, effectively making the data invisible without altering the functionality.
- Limit Search to Column I: You could adjust the `FILTER` and `SEARCH` functions to focus only on column “I”, but this would return results as a single cell or column containing comma-separated data, which may not be ideal for structured output:
=FILTER('New-Bills'!I2:I,ISNUMBER(SEARCH($C$1,'New-Bills'!I2:I)))
This approach condenses the data into one cell or column, which is not practical for viewing in a table format.
- Optimal Solution using QUERY: To solve this and ensure a clear table format, you can use the `QUERY` function. This function enhances your search capabilities, providing the flexibility of retrieving data in a well-structured format without needing an extra column like “I.”
Here’s an improved formula using `QUERY` to achieve dynamic search results across multiple columns:
Use the QUERY function solve the all problems and make the result as your desire way. The QUERY function make easy and more dynamic search result. The best way found after analyzing the all way for the search by keywords in Google Sheets.
=FILTER(QUERY('New-Bills'!B2:I,"Select B,C,D,E,F,G,H",0)ISNUMBER(SEARCH($C$1,'New-Bills'!I2:I)))
- QUERY Function: Retrieves specific columns (B through H) from the “New-Bills” sheet and arranges them in a table format, making the results much more readable and structured. As shown in the image, the results are displayed in an ideal format, eliminating the need to hide column “I” or change the font color to white. By using the QUERY function, you streamline your data filtering and avoid unnecessary steps, while still benefiting from the dynamic search functionality without any visual clutter from extra columns
- FILTER Function: Filters the results of the `QUERY` based on whether the keyword in $C$1 is found in column “I” (which was populated by `TEXTJOIN`).
This approach delivers dynamic search results in a clean, table-like format, resolving the problem of displaying mixed, comma-separated data in a single cell.
Did you like this article?
If you found this post helpful, feel free to share it with your network using the social media buttons on X or via email located on the left below.
YOU MUST KNOW?
- IMPORTRANGE Function in Google Sheets, Benefits and Drawbacks
- Convert PDF Data to Google Sheets with Built-in Formulas
For any questions or assistance regarding the FILTER function or searching keywords in Google Sheets or Excel, feel free to share your queries in the comments. We’re here to help simplify your tasks! The CountLen team is dedicated to providing prompt and effective solutions, ensuring that all your queries are addressed swiftly. If you come across any inaccuracies or misleading information, don’t hesitate to share your feedback in the comments. We’re here to help you make the most of your data!
