How to Use Excel FILTER with SEARCH for Partial Quarter Header Matches in WPS Office
Question details
The user needs to return values with the FILTER function when a quarter header such as Quarter 1 must be matched as part of a longer cell description instead of requiring an exact match.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Filtering a column of longer descriptions by partial quarter header text using FILTER and SEARCH.
- Observed behavior
- An exact-match FILTER condition does not find Quarter 1 inside a longer text string, so a partial-text match is required.
Open the workbook in WPS Office Spreadsheets and confirm that the quarter header text is in G1, the category value is in F2, and the data ranges C2:C13, A2:A13, and B2:B13 contain no blank rows inside the referenced area.
Use FILTER with SEARCH for Partial Quarter Header Text
Use this formula when the header cell contains only part of the description, such as Quarter 1 inside “Sales Quarter 1 Result”.
This formula combines FILTER with ISNUMBER(SEARCH(...)) so the filter condition performs a case-insensitive partial-text match instead of an exact comparison.
In WPS Office Spreadsheets, open the workbook and click the cell where you want the filtered results to spill.
Type =FILTER($C$2:$C$13,($A$2:$A$13=$F2)*ISNUMBER(SEARCH(G$1,$B$2:$B$13))) and press Enter.
Check that C2:C13 is the return column, A2:A13 is the category match column, F2 is the category value, B2:B13 is the description column, and G1 holds the quarter header text.
Fix Excel FILTER Partial Quarter Header Matches in WPS Office Spreadsheets
Use WPS Office Spreadsheets to combine FILTER and SEARCH so a quarter header matches partial text inside longer cells. The formula works directly in .xlsx workbooks and WPS native files without changing your existing workflow.
- 1. Download and install WPS Office: Go to the WPS Office download page, download the free installer, and complete the installation.
- 2. Open your workbook in WPS Spreadsheets: Launch WPS Office Spreadsheets and open the .xlsx or WPS workbook that contains the data.
- 3. Enter the partial-match formula: Click the result cell, enter =FILTER($C$2:$C$13,($A$2:$A$13=$F2)*ISNUMBER(SEARCH(G$1,$B$2:$B$13))), and press Enter.
- 4. Verify the filtered spill range: Confirm that the spill area is empty and the returned values match the category in F2 and the quarter text in G1.
Frequently Asked Questions
Does SEARCH perform a case-insensitive partial match?
Yes, SEARCH is case-insensitive. If you need a case-sensitive partial match, replace SEARCH with FIND and keep it wrapped in ISNUMBER to get a TRUE or FALSE condition.
Why does my FILTER formula return a #CALC! error?
Check that G1 is not empty and that the return or criteria ranges are the same height. Also make sure the spill range where FILTER outputs results has no blocking data.
How do I return multiple columns with this partial match formula?
Change the return range to include more columns, for example =FILTER($C$2:$D$13,($A$2:$A$13=$F2)*ISNUMBER(SEARCH(G$1,$B$2:$B$13))).
Does this formula work in both Microsoft Excel and WPS Office?
Yes, WPS Office Spreadsheets supports FILTER and SEARCH, and it can open and save .xlsx workbooks so the same formula can be used in both applications.
What if the quarter header text contains extra spaces?
Use TRIM around the search text or the searched range, such as SEARCH(TRIM(G$1),$B$2:$B$13), to ignore leading or trailing spaces in the partial match.
