How to Use FILTER and TEXTJOIN for Quarter-Based Results in Excel
Question details
The user needs to build complex Excel formulas using FILTER and TEXTJOIN to extract catalogue items and regional values based on multiple conditions, including specific quarters.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting, filtering, and concatenating specific catalogue data and regional labels filtered by time periods (like Q1'25) and multiple group conditions.
- Observed behavior
- The user requires an array formula structure that filters data across multiple criteria (including quarter matching) and joins the resulting text using a delimiter, with a fallback for empty results.
Ensure your version of Excel or WPS Spreadsheet supports dynamic array functions. The FILTER function is only available in modern spreadsheet versions like Microsoft 365, Excel 2021, and updated WPS Office.
Combine FILTER and TEXTJOIN with Multiple Criteria Logic
Use boolean logic (multiplication) inside the FILTER function to apply multiple criteria, including the specific quarter, and wrap the array in TEXTJOIN to output a single string.
When filtering data by multiple conditions in a spreadsheet, you can use the multiplication operator (*) between logical expressions to act as an AND condition. The TEXTJOIN function will then take the array returned by FILTER and concatenate it using your specified delimiter, allowing you to build clear regional labels.
Start your formula with =TEXTJOIN("; ", TRUE, ...). This sets a semicolon and a space as the delimiter and ignores any empty cells in the final output string.
Define the data you want to return. To append regional tags dynamically, concatenate strings with IF conditions, such as: Catalogue!$B$3:$B$987 & IF(Catalogue!$M$3:$M$987="Y"," UK","").
Add your matching criteria by multiplying conditions together inside parentheses. For example: (Catalogue!$A$3:$A$987=$D$1)*(Catalogue!$C$3:$C$987=$D2)*(Catalogue!$N$3:$N$987="Q1'25").
Include an "if_empty" fallback in the FILTER function, such as "None", to prevent the #CALC! error when no records match the chosen quarter.
Combine the components: =TEXTJOIN("; ",TRUE,FILTER(Catalogue!$B$3:$B$987 & IF(Catalogue!$M$3:$M$987="Y"," UK",""), (Catalogue!$A$3:$A$987=$D$1)*(Catalogue!$N$3:$N$987="Q1'25"), "None")). Press Enter to generate the result.
Use Dynamic Arrays in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and TEXTJOIN. You can effortlessly manage complex datasets, filter records by quarter, and combine text strings without switching to heavy, expensive software.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your catalogue and regional data.
- 2. Enter the formula: Select the target cell and type your combined TEXTJOIN and FILTER formula, applying your specific quarter and group criteria.
- 3. Press Enter: WPS Spreadsheet will process the dynamic array instantaneously, displaying the concatenated regional values.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no results that match your criteria, and no fallback value (like "None") was provided in the formula's last argument.
Can I use an OR condition instead of AND in the FILTER function?
Yes. While the multiplication symbol (*) acts as an AND condition, you can use the addition symbol (+) between your logical criteria arrays to create an OR condition.
Are FILTER and TEXTJOIN available in older versions of Excel?
No. The FILTER function is a dynamic array function available only in Microsoft 365, Excel 2021, and newer versions, as well as modern versions of WPS Office. Older versions require complex INDEX and AGGREGATE array combinations.
