WPS Spreadsheet: Find Multiple Terms in Excel Without Duplicate Row Matches
Question details
The user needs to search a spreadsheet column for multiple slash-separated analysis terms and return each matching row number only once.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A search cell contains several analysis names separated by slashes, and the user wants to compare those terms against a data column.
- Observed behavior
- Wildcard searches can produce false positives, such as a short term like “as” matching “ashes,” and multiple matching terms can otherwise create duplicate results.
Put the source analysis names in one column, such as B2:B100, and enter the slash-separated search terms in a separate cell, such as F1. Confirm that your WPS Spreadsheet version supports dynamic-array functions including LET and TEXTSPLIT.
Use LET, TEXTSPLIT, XMATCH, and BYROW for one result per matching row
Use this formula when the search terms are separated by slashes and wildcard matching is acceptable for the analysis names.
The formula splits the search cell into individual terms, checks every source row, and uses FILTER to return each matching row number once. BYROW combines multiple term matches within the same row, preventing duplicate row numbers.
Select an empty output cell and enter: =LET(data,B2:B100,terms,TRIM(TOROW(TEXTSPLIT($F$1,"/"))),hit,BYROW(data,LAMBDA(cell,--(SUM(--ISNUMBER(XMATCH("*"&terms&"*",cell,2)))>0))),FILTER(ROW(data),hit,"No matches"))
Replace B2:B100 with the actual column containing the analysis names. Keep the range aligned to the rows whose numbers you want returned.
Replace F1 with the cell containing the slash-separated terms, for example as/ashes/complete blood count, then press Enter to spill the matching row numbers.
Use word-boundary-style matching for exact analysis terms
Use this alternative when short terms must match as separate words instead of appearing inside unrelated words.
Search Multiple Spreadsheet Terms and Return Unique Matching Rows in WPS Spreadsheet
WPS Spreadsheet can split slash-separated search terms, evaluate them across a data column, and spill unique matching row numbers with dynamic-array formulas. Its familiar formula interface makes it easy to refine wildcard or word-boundary matching as your data changes.
- 1. Open the workbook: Open the Excel workbook in WPS Spreadsheet and identify the source column and the cell containing slash-separated terms.
- 2. Enter the search formula: Select a blank cell and use the LET, TEXTSPLIT, XMATCH, BYROW, and FILTER formula with your actual source range and search cell.
- 3. Refine matching behavior: Use specific terms for wildcard searches, or switch to the normalized word-boundary formula when short terms create false positives.
- 4. Review the spilled results: Check the returned row numbers and change the source range if new records are added outside the original range.
Frequently Asked Questions
How can I search several slash-separated terms in one spreadsheet column?
Place the terms in a cell such as F1, separated by slashes, and use TEXTSPLIT inside a LET formula to convert them into individual search terms before checking the source column.
Why does a search for “as” also find “ashes”?
The wildcard pattern *as* matches the letters anywhere in a cell, including inside a longer word. Use a more specific term or the boundary-style formula that searches for the term surrounded by spaces.
How does the formula prevent duplicate row numbers?
BYROW produces one true or false result for each source row, even when several search terms match that row. FILTER then returns the row once instead of returning one result per term.
Can I return the matching analysis names instead of row numbers?
Yes. Replace ROW(data) in the FILTER formula with data, or with another aligned range containing the labels you want to display.
What should I do if TEXTSPLIT or dynamic arrays are unavailable?
Update WPS Office to a version that supports these functions, or use helper columns with SEARCH and a manual OR-style test for each term. Dynamic-array support is required for the single-cell formula to spill results automatically.
