Use TEXTJOIN and FILTER in WPS Spreadsheets to Combine Matching Values from Another Sheet
Question details
Find a value in cell G29 in column B of the Auto NC worksheet and combine all corresponding values from column H into a comma-separated result.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to return multiple matching values from another worksheet in a single cell.
- Observed behavior
- Full-column references can slow the calculation, while unmatched data types or extra spaces can cause FILTER to return an empty result.
Confirm that the lookup value is in G29 and that the Auto NC sheet uses column B for matching values and column H for returned values. Use matching bounded ranges with the same first and last row in both FILTER arguments.
Use TEXTJOIN with FILTER and bounded worksheet ranges
Use this formula when the values in Auto NC column B match the value in G29 exactly and you want all related column H values in one cell.
FILTER returns every value in the specified column H range whose corresponding column B value equals G29. TEXTJOIN then combines the returned values and ignores blanks.
Click the cell where you want the comma-separated list to appear in WPS Spreadsheets.
Type or paste =TEXTJOIN(",",TRUE,FILTER('Auto NC'!$H$1:$H$10000,'Auto NC'!$B$1:$B$10000=G29,"")) and press Enter.
Replace 10000 with the last row of your source data, ensuring that 'Auto NC'!$H$1:$H$10000 and 'Auto NC'!$B$1:$B$10000 cover identical row numbers.
Normalize spaces and value types before matching
Use this version when the formula returns blank even though the values appear to match, such as when cells contain leading spaces or numbers stored as text.
Combine Multiple Matching Worksheet Values with WPS Spreadsheets
WPS Spreadsheets lets you use TEXTJOIN and FILTER to find all records matching a lookup value and return their related values as one comma-separated result. Use bounded ranges to keep cross-sheet formulas responsive in larger files.
- 1. Open the workbook in WPS Spreadsheets: Open your Excel or WPS workbook, then select the worksheet containing the lookup value in G29.
- 2. Choose an output cell: Click the empty cell where you want the matching Auto NC values to be combined.
- 3. Add the TEXTJOIN and FILTER formula: Enter =TEXTJOIN(",",TRUE,FILTER('Auto NC'!$H$1:$H$10000,'Auto NC'!$B$1:$B$10000=G29,"")) in the formula bar.
- 4. Use normalized matching if required: If visible matches return no values, replace the comparison with TRIM('Auto NC'!$B$1:$B$10000&"")=TRIM(G29&"").
Frequently Asked Questions
Why does FILTER return an empty result when the lookup value looks correct?
The values may differ by leading or trailing spaces or by data type. Use TRIM on both values and append &"" to compare them as text: TRIM('Auto NC'!$B$1:$B$10000&"")=TRIM(G29&"").
How can I change the separator between combined values?
Change the first TEXTJOIN argument. For example, use TEXTJOIN("; ",TRUE,...) for semicolons with spaces, or TEXTJOIN(CHAR(10),TRUE,...) for line breaks and enable Wrap Text from Home > Wrap Text.
Why should I avoid using Auto NC!B:B and Auto NC!H:H?
Whole-column references make FILTER evaluate a very large number of cells and can slow workbook calculations. Use matching bounded ranges, such as $B$1:$B$10000 and $H$1:$H$10000, or use table columns.
What happens if more than one row in Auto NC matches G29?
FILTER returns every matching value from column H, and TEXTJOIN combines all nonblank returned values into the same output cell.
Can I use this formula with an Excel workbook in WPS Spreadsheets?
Yes. Open the .xlsx or .xls workbook in WPS Spreadsheets, enter the formula in the destination cell, and save the file in a compatible Excel format if it will be shared with Excel users.
