How to Return Multiple Matching Asset References from Comma-Separated Codes in WPS Spreadsheets
Question details
The user needs a formula that splits one or more comma-separated codes in a Sites cell, finds every matching source row, and returns the corresponding Asset Reference values combined in one cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A Sites column contains cells with one or more comma-separated codes, and the user wants to look up and combine all related Asset Reference values from another sheet.
- Observed behavior
- The goal is to display all matching Asset Reference values in a single cell with a separator such as ' / ' for each Sites row.
Confirm that your WPS Spreadsheets version supports dynamic array functions such as TEXTSPLIT, FILTER, BYROW, and LAMBDA. Also verify that the Sites column uses a comma as the delimiter and that lookup codes and source values do not contain unintended leading or trailing spaces.
Use TEXTSPLIT, BYROW, FILTER, and TEXTJOIN to Return All Matches
Recommended for dynamic array formulas in WPS Spreadsheets when you need every matching row combined into one cell, even if multiple source rows match the same code.
This formula is the most robust because BYROW processes each source row against all split codes, so rows matching any of the comma-separated codes are returned.
Open your workbook in WPS Spreadsheets and click the first result cell, for example K2 on the Master sheet.
Click the formula bar and enter =TEXTJOIN(" / ",TRUE,FILTER(Sheet1[Asset Reference],BYROW(Sheet1[Current Parent]=TEXTSPLIT([@Sites],","),LAMBDA(row,OR(row))),"")).
If your source data is not in a table, replace Sheet1[Asset Reference] with a range such as Sheet1!$A$2:$A$1000 and Sheet1[Current Parent] with Sheet1!$E$2:$E$1000.
Press Enter to commit the formula, then select K2 and drag the fill handle down through the remaining Sites rows.
Use TEXTSPLIT and XLOOKUP with TEXTJOIN for a Shorter Formula
Use this alternative when each lookup code appears only once in the source range and you do not need BYROW row-level OR logic.
Return All Matching Asset References with WPS Spreadsheets Dynamic Array Formulas
In WPS Spreadsheets, combine TEXTSPLIT, BYROW, FILTER, and TEXTJOIN to split comma-separated codes, find every matching row, and display the related Asset Reference values in one cell without manual filtering.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook containing the Sites and source data.
- 2. Select the first result cell: Go to the Master sheet and click the cell where you want the combined asset references, such as K2.
- 3. Enter the recommended formula: Click the formula bar, paste =TEXTJOIN(" / ",TRUE,FILTER(Sheet1[Asset Reference],BYROW(Sheet1[Current Parent]=TEXTSPLIT([@Sites],","),LAMBDA(row,OR(row))),"")), and press Enter.
- 4. Fill the formula down: Use the fill handle on K2 to copy the formula to the remaining data rows.
- 5. Review the combined references: Check that each Sites cell now shows the expected Asset Reference values separated by ' / '.
Frequently Asked Questions
Why do I get #NAME? when entering the formula?
The #NAME? error usually means one of the functions is not recognized. Use a version of WPS Spreadsheets that supports TEXTSPLIT, FILTER, BYROW, and LAMBDA, or use the XLOOKUP-based alternative in this article.
Can I change the separator from ' / ' to a comma or semicolon?
Yes. In TEXTJOIN, replace the first argument with any delimiter, for example ", " or "; ". The delimiter must be wrapped in quotation marks.
What should I do if the source data has extra spaces around the codes?
Wrap the codes or source column with TRIM. For example, use TRIM(TEXTSPLIT([@Sites],",")) or compare against TRIM(Sheet1[Current Parent]).
Does this formula spill into multiple cells?
No. The formula returns a single text value. FILTER and BYROW compute the matches, and TEXTJOIN combines them into one cell.
Can I use this formula with a normal range instead of an Excel table?
Yes. Replace the structured references such as Sheet1[Asset Reference] with normal ranges like Sheet1!$A$2:$A$1000 and Sheet1!$E$2:$E$1000.
