Use Excel FILTER to Display Related Rows from Another Worksheet
Question details
Display every row from a related worksheet where the unique ID matches the ID selected on the current worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A selected ID in Sheet1 needs to dynamically return all matching records stored in Sheet2.
- Observed behavior
- The result list should automatically update whenever the selected ID changes.
Confirm that the selected ID and the ID column in the source worksheet use the same data type, such as both text or both numbers. Make sure the formula output area has enough empty cells for the returned rows to spill.
Use FILTER to return all rows matching the selected ID
Use this formula when the selected ID is in Sheet1!A2 and the related records are stored in Sheet2 with IDs in column A.
FILTER creates a dynamic list of every source row that meets the condition. When the value in Sheet1!A2 changes, the returned rows recalculate automatically.
On Sheet1, click the cell where the matching records should begin, such as C2.
Type =FILTER(Sheet2!A2:Z1000,Sheet2!A2:A1000=A2,"No matches") and press Enter. Change A2:Z1000 to the data columns you want to return.
Allow the formula to fill the cells below and to the right. If a spill error appears, clear any values or merged cells blocking the output range.
Select or enter a different ID in Sheet1!A2 to refresh the related rows automatically.
Match the returned range and criteria range sizes
Use this check when FILTER returns an error or does not evaluate the intended rows.
Filter Related Worksheet Records in WPS Spreadsheets
WPS Spreadsheets lets you use dynamic formulas to return records associated with a selected ID. Enter a FILTER formula in the destination worksheet, reference the source data range and ID column, and the result list updates when the selected ID changes.
- 1. Open the workbook in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet that contains the selected ID.
- 2. Choose an output cell: Click an empty cell where the filtered records should start, such as C2 on Sheet1.
- 3. Enter the FILTER formula: Enter =FILTER(Sheet2!A2:Z1000,Sheet2!A2:A1000=A2,"No matches") and press Enter.
- 4. Keep the spill area clear: Remove values or merged cells from the cells where the formula needs to display its matching results.
- 5. Update the selected ID: Change the value in Sheet1!A2 to display records for another ID automatically.
Frequently Asked Questions
Why does the FILTER formula show a spill error?
FILTER needs empty cells for every returned row and column. Clear values, formulas, or merged cells from the output area, then enter the formula again.
Can I return only selected columns from Sheet2?
Yes. Change the first FILTER argument to the columns you want to display. For example, use Sheet2!B2:E1000 to return columns B through E while keeping Sheet2!A2:A1000 as the ID criteria range.
What does the "No matches" text in the formula do?
The third FILTER argument is the optional result shown when no rows match the selected ID. You can replace "No matches" with any message or use an empty string, "", to show a blank result.
Why are matching IDs not being returned?
Check that Sheet1!A2 and the IDs in Sheet2 column A have matching formats. Text values with extra spaces, numbers stored as text, or differing leading zeros can prevent a match.
