Extract Matching Rows to Another Range

Excel Formulas › Dynamic Arrays

Excel 365Legacy alt

Pulling every record that matches a condition into a separate report area — all of one rep’s orders, every overdue invoice — is what FILTER was built for. For Excel 2019 and older, an INDEX/SMALL array does the same job.

Quick formula: to pull every row of A2:C9 where rep (B) equals F1:
=FILTER(A2:C9, B2:B9=F1, "None")
The matching rows spill into your report area, columns intact, updating live as the source changes.

Functions used (tap for the full reference guide):

FILTERINDEXSMALL

The example

An orders table. We extract just Ana’s rows into a report block.

ABC
1OrderRepAmount
21001Ana$120
31002Ben$80
41003Ana$200
51004Cy$60
61005Ana$90

The formula

One formula pulls all of Ana’s orders:

=FILTER(A2:C6, B2:B6="Ana", "None") // returns orders 1001, 1003, 1005

How it works

FILTER returns whole rows that pass the test:

  1. The first argument, A2:C6, is the full set of columns to return for each match.
  2. The second, B2:B6="Ana", marks which rows qualify (TRUE/FALSE per row).
  3. Matching rows spill into the report area below the formula — order number, rep, and amount together.
  4. The third argument, "None", shows instead of a #CALC! error when nobody matches.

Return only some columns. Wrap FILTER in CHOOSECOLS to pull, say, just order number and amount: =CHOOSECOLS(FILTER(A2:C6, B2:B6="Ana"), 1, 3).

Try it: interactive demo

Live demo

Pick a rep; their rows are extracted into the report block.

Variations

Legacy: INDEX / SMALL / IF (Excel 2019)

The classic array formula, entered with Ctrl+Shift+Enter and filled down, returns the nth match:

=IFERROR(INDEX(A$2:A$6, SMALL(IF($B$2:$B$6=$F$1, ROW($B$2:$B$6)-ROW($B$2)+1), ROWS($A$2:A2))), "")

Two conditions

Multiply for AND — Ana’s orders of at least $100:

=FILTER(A2:C6, (B2:B6="Ana")*(C2:C6>=100), "None")

Sort the extracted rows

Wrap in SORT to order the report — biggest first:

=SORT(FILTER(A2:C6, B2:B6="Ana"), 3, -1)

Pitfalls & errors

#SPILL! The report area isn’t clear. FILTER needs empty cells to write the matching rows into.

#CALC! on no matches. Supply the third argument (e.g. "None") so an empty result is handled.

The legacy INDEX/SMALL formula is fragile. It must be array-entered and the ranges locked exactly. If you have Excel 365, FILTER is far simpler and safer.

Practice workbook

📊
Download the free Extract Matching Rows to Another Range practice workbook
The orders table with live FILTER extraction, the AND and SORT variants, and the legacy INDEX/SMALL method, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I extract all rows that match a criterion in Excel?
Use =FILTER(A2:C9, B2:B9=F1, "None"). It returns every matching row with all columns and spills them into your report area, updating automatically. Available in Excel 365 and 2021.
How do I extract matching rows in Excel 2019?
Use an array formula with INDEX, SMALL, IF and ROW, entered with Ctrl+Shift+Enter and filled down, or use Advanced Filter to copy matching rows to another location.
How do I return only some columns of the matches?
Wrap FILTER in CHOOSECOLS: =CHOOSECOLS(FILTER(A2:C6, B2:B6="Ana"), 1, 3) returns just the 1st and 3rd columns of the matching rows.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Filter data with a formula · Unique sorted list · Lookup with multiple criteria

Function references: FILTER · INDEX · SMALL