How to Use TEXTJOIN with FILTER and UNIQUE for Risk-Based Results in WPS Office Spreadsheets
Question details
The user needs a formula that returns unique results for each location only when the associated Risk value is greater than 3, with results spilling for the locations listed in F2:F5.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A risk assessment table contains locations in column A, result values in column B, and numeric risk scores in column C; the user wants location-specific unique result lists that only include rows exceeding a risk threshold.
- Observed behavior
- The current approach does not consistently combine the risk condition with unique filtering and spill results for the F2:F5 location list.
Confirm that the data is arranged in contiguous columns, for example Location in A2:A13, Result in B2:B13, and Risk in C2:C13. Make sure the Risk values in column C are real numbers and that F2:F5 contains the exact location values you want to evaluate.
Use BYROW, LAMBDA, TEXTJOIN, UNIQUE, and FILTER to spill risk-based results by location
Best for WPS Office versions or Microsoft 365 environments that support BYROW and LAMBDA, because it returns all location results with a single dynamic array formula.
This formula checks each location in F2:F5, multiplies two conditions to keep rows where the location matches and Risk is greater than 3, removes duplicate Result values, and joins them with a comma and space.
Click cell G2, the first empty cell where location-specific results should appear.
In the formula bar, enter =BYROW($F$2:$F$5,LAMBDA(v,TEXTJOIN(", ",,UNIQUE(FILTER($B$2:$B$13,($A$2:$A$13=v)*($C$2:$C$13>3),""))))).
Press Enter. The formula should spill results into G2:G5 for the locations in F2:F5.
Use a per-row TEXTJOIN, UNIQUE, and FILTER formula and copy it down
Use this method when BYROW/LAMBDA is not available or when you prefer a simpler formula for each location cell.
Create Risk-Based Unique Result Lists in WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your Excel workbook, apply TEXTJOIN, UNIQUE, and FILTER formulas, and create location-specific risk-based result lists without changing your file format.
- 1. Open your workbook: Launch WPS Office Spreadsheets and open the .xlsx file containing the Location, Result, and Risk columns.
- 2. Select the result start cell: Click G2 or the cell where you want the first location result to appear.
- 3. Enter the formula: Use either the BYROW/LAMBDA dynamic array formula or the per-row TEXTJOIN, UNIQUE, and FILTER formula as shown above.
- 4. Fill down if needed: If you used the per-row formula, drag the fill handle from G2 to G5 to generate results for all locations.
- 5. Save the file: Save the workbook as .xlsx to preserve the formulas and share it with Microsoft Office users.
Frequently Asked Questions
Why does the formula return a #NAME? error?
A #NAME? error usually means one of the functions such as FILTER, UNIQUE, BYROW, or LAMBDA is not available in your current WPS Office version or Excel version. Update WPS Office, or use the per-row TEXTJOIN/FILTER/UNIQUE formula without BYROW and LAMBDA.
Can I use this formula if the Risk values are stored as text?
No, the condition C2:C13>3 requires numeric risk values. If they are text, convert the column to numbers or wrap the risk reference in VALUE, for example VALUE(C2:C13)>3.
Do I need to press Ctrl+Shift+Enter?
In Microsoft 365 and WPS Office versions with dynamic array support, a normal Enter is enough. In older array-formula environments, select the output range and confirm with Ctrl+Shift+Enter if required, or use the fill-down method.
What happens if no rows match the location and Risk condition?
The FILTER function returns an empty string, and TEXTJOIN normally returns a blank result for that location. You can adjust the empty argument to a custom message such as "No match" if needed.
