How to Return Matching Rows from Another Excel Sheet by Employee Name in WPS Office
Question details
User needs to pull all sales records from a source sheet into a destination sheet based on an employee name match, with each matching record shown in its own row.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The destination sheet has an employee name, and the source sheet contains multiple sales rows for that employee. The goal is to return every matching row dynamically.
- Observed behavior
- Standard lookup formulas such as VLOOKUP return only the first match, so multiple sales records are not displayed in separate rows.
Confirm that the source and destination sheets are in the same workbook and that employee names use consistent spelling, capitalization, and cell formatting. Identify the source data range and the destination cell where the first matching row should start.
Use the FILTER Function to Return All Matching Rows Automatically
Best for modern Excel and WPS Office versions that support dynamic arrays. FILTER spills all matching sales records into separate rows without copying formulas.
FILTER is the simplest method when your version supports dynamic arrays. It returns every row that meets the employee name condition and updates automatically when source data changes.
Click the cell where the first returned row should appear, such as A2 on the destination sheet.
Type =FILTER(SourceSheet!A2:D100,SourceSheet!A2:A100=B2,"No matching records") and press Enter. Replace SourceSheet, A2:D100, and B2 with your actual sheet name, source table range, and employee name cell.
Leave enough empty cells below and to the right for the spilled rows. WPS or Excel will fill the matching records automatically.
Use INDEX, SMALL, and IF to Return Matching Rows in Older Excel or WPS Versions
Use this formula when FILTER is not available. It returns the first, second, and subsequent matching rows as you copy the formula down.
Return Matching Employee Rows in WPS Spreadsheets with FILTER or INDEX-SMALL-IF
Use WPS Spreadsheets to build the same FILTER or INDEX-SMALL-IF formulas and pull every matching sales record from a source sheet by employee name. WPS supports Excel-compatible formulas, so you can manage the workbook without changing your workflow.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the .xlsx file that contains the source and destination sheets.
- 2. Select the destination cell: Go to the destination sheet and click the first cell where matching rows should appear, such as A2.
- 3. Insert the FILTER formula: Type =FILTER(SourceSheet!A2:D100,SourceSheet!A2:A100=B2,"No matching records") and press Enter. Change the sheet name and ranges to match your data.
- 4. Verify the spilled rows: Confirm that all matching sales records appear in separate rows. If FILTER is not available, use the INDEX-SMALL-IF formula from Solution 2.
- 5. Save the workbook: Press Ctrl+S or click Menu > Save to keep the file in .xlsx format.
Frequently Asked Questions
Why does VLOOKUP return only the first matching row when I look up an employee name?
VLOOKUP stops at the first match. To return every matching row, use FILTER, or INDEX with SMALL and IF and copy the formula down.
Can I return matching rows from another sheet without using macros?
Yes. Use a formula such as FILTER when your version supports dynamic arrays, or an INDEX-SMALL-IF array formula in older versions. No VBA is required.
How do I make the formula show a blank instead of #N/A when no more matches exist?
Wrap the formula in IFERROR, for example =IFERROR(...,""), or use the third argument of FILTER to display a custom message.
Why is my INDEX-SMALL-IF formula not returning the next matching record?
Make sure the ROWS($1:1) part starts in the first formula cell and is not locked as ROWS($1:$1). Also enter the formula with Ctrl+Shift+Enter in older Excel or WPS versions.
Does WPS Spreadsheets support the FILTER function?
WPS Spreadsheets supports many modern Excel functions and opens .xlsx workbooks with these formulas. If your WPS version does not support FILTER, use the INDEX-SMALL-IF method.
