Extract a Submatrix from an Excel Matrix Using Formulas
Question details
Extract all matrix rows associated with a selected priority into a submatrix, including an option for growing datasets.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A priority column identifies the rows to return, and a separate cell contains the selected priority.
- Observed behavior
- The desired submatrix contains the values from every row whose priority matches the selection.
Arrange priorities in column A and the values to extract in columns B through D, with headers in row 1. Enter the priority to match in G1 and leave the output area starting at H2 empty.
Use FILTER to return matching rows automatically
Use this option in an Excel version that supports dynamic arrays when you want the results to spill automatically.
Type the priority you want to extract in cell G1.
In H2, enter =FILTER(B2:D100,A2:A100=G1,"") and press Enter. The matching rows spill across and down.
Select the source data and choose Insert > Table. If the table is named Table1 and its headers are Priority, Value1, Value2, and Value3, replace the formula in H2 with =FILTER(Table1[[Value1]:[Value3]],Table1[Priority]=G1,"").

Use AGGREGATE with a growing Excel Table
Use an ordinary formula that can be copied across and down when FILTER is unavailable and the source data will grow.
Use a legacy array formula for fixed ranges
Use this method in older Excel versions that support array formulas but do not offer FILTER.
Extract a Priority-Based Submatrix in WPS Spreadsheet
Use an INDEX and SMALL array formula in WPS Spreadsheet to return the rows matching a selected priority, then copy it across and down.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheet and place priorities in column A and source values in columns B through D.
- 2. Choose a priority: Enter the priority to match in G1 and clear the output area starting at H2.
- 3. Enter the formula: In H2, enter =IFERROR(INDEX(B$2:B$100,SMALL(IF($A$2:$A$100=$G$1,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($H$2:H2))),"") and confirm it as an array formula with Ctrl+Shift+Enter.
- 4. Fill the results: Copy H2 across to J2 and down to display the matching rows. Extend the source ranges if data grows beyond row 100.

Frequently Asked Questions
How can I include new source rows automatically?
Select the source range and choose Insert > Table, then use structured references in the FILTER or AGGREGATE formula. New records added to the table are included in those references.
Why does my FILTER formula show #SPILL!?
Check the cells below and to the right of the formula for content blocking the results. Clear the output area and place the formula outside the source table.
Do I need Ctrl+Shift+Enter for every formula?
The legacy INDEX and SMALL formula requires Ctrl+Shift+Enter in older Excel versions. The AGGREGATE formula can be entered normally, and FILTER uses a normal Enter in supported Excel versions.
Can I use full-column references instead of a table?
Full-column references may slow array calculations because they evaluate many unused cells. An Excel Table is a better way to include new rows without hard-coding a last row.




