WPS Spreadsheet: Return Multiple Exact Matches and Row Numbers
Question details
The user needs a formula that returns every exact match from column A and combines the matching row numbers into a single result without VBA or macros.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains searchable values in column A. The sheet name is stored in F5, and the value to find is stored in G5.
- Observed behavior
- The goal is to display all matching references, such as A12, A18, and A25, in one result together with the searched value.
Confirm that F5 contains the exact worksheet name and G5 contains the value to search for. Make sure your WPS Spreadsheet version supports the FILTER and TEXTJOIN functions.
Use FILTER and TEXTJOIN to combine every matching row
Use this dynamic-array formula when you need all exact matches from column A returned in one cell without macros.
The formula builds a reference from column A and each matching row number, filters out nonmatching rows, and joins the remaining references with commas. Replace the full-column references with a defined range such as A1:A10000 if the workbook becomes slow.
In cell F5, enter the exact name of the worksheet that contains the search data.
In cell G5, enter the value that should be matched in column A.
Select the result cell and enter =F5&": "&TEXTJOIN(", ",TRUE,FILTER("A"&ROW(INDIRECT("'"&F5&"'!A:A")),INDIRECT("'"&F5&"'!A:A")=G5,""))&" - "&G5, then press Enter.
Check the result cell for a value such as Sheet1: A12, A18 - ProductX. If no match exists, FILTER returns an empty result.
Find and combine exact-match rows in WPS Spreadsheet
WPS Spreadsheet can complete this lookup with modern worksheet formulas. Enter the source sheet and search value, then use FILTER with TEXTJOIN to return every matching row reference in one cell without VBA.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the lookup data.
- 2. Set the lookup inputs: Enter the source worksheet name in F5 and the value to find in G5.
- 3. Add the dynamic formula: Select a result cell and enter the FILTER and TEXTJOIN formula to collect matching column A row references.
- 4. Save the result: Review the combined matches and save the workbook in XLSX format if it must be shared with Microsoft Excel users.
Frequently Asked Questions
Where should I place the formula in WPS Spreadsheet?
Place it in any result cell outside the source data, such as H5. Keep the sheet name in F5 and the lookup value in G5 so the formula can use those inputs.
How does the formula return row numbers for exact matches?
The expression "A"&ROW(...) converts each matching row into a reference such as A12. FILTER keeps only rows where column A equals G5, and TEXTJOIN combines those references.
Can I search a limited range instead of the entire column?
Yes. Replace both instances of A:A inside INDIRECT with a range such as A2:A10000. Use the same range in both places so the values and row numbers stay aligned.
What happens if no exact match is found?
FILTER uses an empty string as its if-empty argument, so TEXTJOIN returns no matching row reference. Verify the spelling, spacing, worksheet name, and value in G5 if the result is blank.
Can this formula be used without VBA or macros?
Yes. The solution uses worksheet functions only: INDIRECT, ROW, FILTER, and TEXTJOIN. It does not require VBA, macros, or a separate script.
