Function Problems

Extract a Submatrix from an Excel Matrix Using Formulas

John WilsonJohn Wilson Nov 13, 2026 868 views

Question details

Extract all matrix rows associated with a selected priority into a submatrix, including an option for growing datasets.

Extract a Submatrix from an Excel Matrix Using Formulas
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the selection

Type the priority you want to extract in cell G1.

2
Enter the formula

In H2, enter =FILTER(B2:D100,A2:A100=G1,"") and press Enter. The matching rows spill across and down.

3
Allow for growth

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 FILTER to return matching rows automatically
Keep the spill area clear: Existing content in the output area can cause a #SPILL! error. Clear the obstructing cells, and keep the formula outside the source table.
Extract matching rows in WPS

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. 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. 2. Choose a priority: Enter the priority to match in G1 and clear the output area starting at H2.
  3. 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. 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.
Extract matching rows into a separate submatrix.Open and save Microsoft Excel XLSX files.Work with familiar spreadsheet formulas and cell references.
microsoft office alternative - wps office

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.