How to Keep Entire Excel Rows Together When Matching Duplicate Values in WPS Office
Question details
The user needs a row-level lookup that returns an entire source row when duplicate values match between two columns, instead of a single-cell lookup that separates related columns.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has duplicate IDs or values in one column and needs to pull all related cells from the matching source row into another area.
- Observed behavior
- A standard VLOOKUP or simple cell lookup returns only one cell or column, so the associated data across the complete row becomes separated.
Confirm that the lookup key column is present in both the source and result areas, and make sure your version of WPS Office supports dynamic array functions such as FILTER and XLOOKUP before you enter spill formulas.
Return All Matching Columns Together with FILTER
Use this when you want every matching row to spill across columns D:AN automatically without manually copying each column.
The FILTER function returns the complete source range for all rows whose key column equals the lookup value, so associated columns remain aligned.
Click the cell where you want the returned row block to start, such as the cell next to your first lookup value.
Type =FILTER($D$2:$AN$100,$D$2:$D$100=B2) and adjust $D$2:$AN$100 to your full source data range and B2 to the first lookup value.
Press Enter. WPS Spreadsheets spills the matching rows across as many columns and rows as needed; ensure the spill area is empty to avoid a #SPILL! error.
Return Duplicate Matches One Row at a Time with an Occurrence Counter
Use this when duplicate values should be returned individually rather than as a spilled block, such as repeated IDs or line items.
Complete Row Lookups with Duplicate Values in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-compatible FILTER and XLOOKUP formulas, so you can return entire rows when duplicate values match and keep all related data in place for free.
- 1. Download and install WPS Office: Go to the official WPS Office site, download WPS Office, and open WPS Spreadsheets.
- 2. Open your Excel workbook: Open the .xlsx file that contains the duplicate values and the source data range.
- 3. Create your source row lookup: Select the first result cell and enter =FILTER($D$2:$AN$100,$D$2:$D$100=B2) to return all matching rows.
- 4. Adjust for individual duplicates: If duplicates must be returned one at a time, add an occurrence counter and include it in the FILTER condition.
- 5. Verify the returned rows: Check that the returned rows keep every column from D to AN aligned with the matching value.
Frequently Asked Questions
Why does VLOOKUP separate my row data when duplicate values match?
VLOOKUP is designed to return a single value from one specified column, so it cannot keep an entire row together. Use FILTER returning the full source range, such as =FILTER($D$2:$AN$100,$D$2:$D$100=B2), or use XLOOKUP with a range as the return array.
Can WPS Office use the FILTER function to return entire rows?
Yes, compatible WPS Office versions support FILTER. Enter =FILTER($D$2:$AN$100,$D$2:$D$100=B2), replace the ranges with your data, and make sure the spill area is clear.
How do I return duplicate matches one at a time instead of all at once?
Add a helper column in the source data with =COUNTIFS($D$2:D2,D2) and fill it down. Then use FILTER with both the original value and the occurrence number, for example =FILTER($D$2:$AN$100,($D$2:$D$100=B2)*($A$2:$A$100=F2)).
What causes the #SPILL! error in WPS Spreadsheets?
A #SPILL! error occurs when existing data blocks the range that the dynamic array formula needs. Clear the cells to the right and below the formula cell so the complete row block can spill.
Does WPS Office support XLOOKUP for row-level lookups?
WPS Office supports XLOOKUP in compatible versions. Use =XLOOKUP(B2,$D$2:$D$100,$D$2:$AN$100) to return the whole source row for the first matching value.
