Return Every Matching Row in Excel 2013 Without FILTER

In Excel 2013, record each matching row’s position in a helper column, then use SMALL to request the first, second, third and subsequent positions. INDEX returns the corresponding values. The helper method below uses ordinary Enter, not Ctrl+Shift+Enter. A separate legacy array alternative follows if a helper column is unsuitable.

This returns records in source order. It does not remove duplicates, sort the results, or aggregate their quantities. No macro, add-in or external connection is needed.

Set up an eight-record example

Download the synthetic multiple-matches CSV, or import these records into A1:C9 of a blank worksheet:

Team,Item,Units
North,Pen,12
South,Pad,5
North,Box,7
East,Tape,0
North,Folder,3
South,Pen,8
North,Pen,2
East,Box,4

Keep one source record per row and confirm that Units contains numbers. Use a comma delimiter when importing the CSV so each field lands in its own column. The repeated North/Pen record and East/Tape quantity of zero are intentional tests. CSV contains only the input records, not the formulas or workbook formatting. Save your working copy as .xlsx after adding formulas.

Use this layout:

• D1: Match position
• F1: Team to return; F2: North
• F4: Matching records; F5: the count formula below
• G1: Item; H1: Units

The formulas use English function names and commas. Your Excel locale may require localized names or semicolon separators.

1. Make the matching positions visible

Enter this in D2 with ordinary Enter, then fill down to D9:

=IF(A2=$F$2,ROW()-ROW($A$2)+1,"")

For North, D2:D9 should contain 1, blank, 3, blank, 5, blank, 7, blank. These are positions within the data, not worksheet row numbers: the first record is position 1 even though it is on worksheet row 2. The absolute $F$2 keeps the selector fixed when copying. ROW guide; Microsoft ROW reference.

In F5 enter:

=COUNT(D2:D9)

Expected result: 4. COUNT counts the numeric matching positions and ignores the helper’s empty text results. This count will prevent requests for a nonexistent fifth match. Microsoft COUNT reference.

2. Return the item and quantity

Enter this in G2 with ordinary Enter:

=IF(ROWS($G$2:G2)>$F$5,"",INDEX($B$2:$B$9,SMALL($D$2:$D$9,ROWS($G$2:G2))))

Enter this in H2, also with ordinary Enter:

=IF(ROWS($G$2:G2)>$F$5,"",INDEX($C$2:$C$9,SMALL($D$2:$D$9,ROWS($G$2:G2))))

Select G2:H2 and copy down through row 9. These are eight separate output rows; Excel 2013 does not automatically spill this formula into a variable-sized result area.

ROWS($G$2:G2) starts at 1 and becomes 2, 3 and so on as the formula is copied downward. SMALL chooses that numbered matching position. INDEX returns the item or quantity at that position. The opening IF leaves surplus output rows empty when the requested position exceeds F5. It does not suppress unrelated errors with a blanket IFERROR.

See the INDEX, SMALL and ROWS guides. Microsoft’s function references document INDEX’s row selection, SMALL’s numbered smallest value, and ROWS’ range height.

For North, G2:H5 should be:

G6:H9 should appear empty. Both Pen records must remain; the formula returns matching rows, not unique item names.

3. Test changes, zero and no-match cases

Change F2 and check both the count and every returned field:

Return F2 to North, change C2 temporarily from 12 to 20, and check that the first returned quantity becomes 20. Restore 12 afterward. A correct initial display alone does not establish that a changing report works.

Keep F5 visible so that no matches can be distinguished from an output area that was never filled down. If results remain stale, check Formulas > Calculation Options > Automatic and the workbook’s formulas before trusting them. Changing calculation mode can affect other open workbooks; use an isolated test workbook when diagnosing this.

Alternative: no helper column, using legacy array entry

Use a separate copy of the example. Replace F5 with this ordinary-Enter count:

=SUMPRODUCT(--($A$2:$A$9=$F$2))

The double minus converts comparison results to 1 and 0 before summing them. SUMPRODUCT guide; Microsoft SUMPRODUCT reference.

In G2 use the following formula, confirming it with Ctrl+Shift+Enter in Excel 2013:

=IF(ROWS($G$2:G2)>$F$5,"",INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$F$2,ROW($A$2:$A$9)-ROW($A$2)+1,""),ROWS($G$2:G2))))

For H2 use this formula, also confirmed with Ctrl+Shift+Enter:

=IF(ROWS($G$2:G2)>$F$5,"",INDEX($C$2:$C$9,SMALL(IF($A$2:$A$9=$F$2,ROW($A$2:$A$9)-ROW($A$2)+1,""),ROWS($G$2:G2))))

Enter each formula in its single starting cell, then copy G2:H2 down through row 9. Do not select the entire output block and create one multi-cell array formula. Excel supplies the formula’s enclosing braces after correct legacy array entry; do not type those braces yourself. If you edit a legacy array formula, reconfirm it with Ctrl+Shift+Enter. Microsoft’s legacy array-entry guidance.

This produces the same expected records as the helper method, but the intermediate positions are harder to inspect. Prefer the visible helper when someone else will maintain the workbook.

Modern alternative: FILTER

For Microsoft 365, Excel 2021 or Excel 2024, FILTER can return both columns from one formula:

=FILTER(B2:C9,A2:A9=F2,"No matches")

Try it in G2 on a fresh copy with an empty output area, outside an Excel table. Use ordinary Enter and let the result spill; do not copy it down. Existing formulas in the destination cells can obstruct the spill, so do not paste it over the legacy output block and expect those formulas to disappear. The no-match case displays No matches rather than the legacy method’s empty rows.

FILTER is not available in Excel 2013, 2016 or 2019. Microsoft’s FILTER documentation lists the modern editions and explains its spill behavior. A Microsoft 365 demonstration of FILTER is not evidence that it works in Excel 2013.

Troubleshooting and limits

• Only the first result appears: check that the legacy output was copied down. Its capacity is the number of output rows you filled, not the number in F5.
• The same result repeats: inspect the expanding $G$2:G2 reference. Locking both ends at $G$2:$G$2 would keep requesting match 1.
• Wrong record or #REF!: positions must be relative to B2:B9 and C2:C9, whose first record is position 1. Using raw worksheet row numbers creates an offset error.
• #NUM! from SMALL: check F5, the helper positions, and output ranks. Requesting more numeric positions than exist is invalid. Do not hide it before diagnosing the mismatch.
• A valid team returns nothing: check spelling, trailing spaces and selector references. Ordinary text equality is case-insensitive here; this is not a case-sensitive identifier matcher.
• A blank source value returns 0: a direct INDEX reference to an empty cell can display zero. The fixture deliberately contains a genuine numeric zero. Decide how missing data should be represented before adding a blanket “hide zeros” rule.
• New source rows do not appear: this example intentionally uses fixed A2:C9 ranges. Extend the helper into each new row, expand the count and all source/helper ranges together, and provide enough output rows. Avoid full-column legacy array calculations in a large workbook.
• Source error values: resolve them deliberately. This method does not promise to skip errors in keys or returned values.

Version and verification notes

The helper and Ctrl+Shift+Enter workflows use legacy functions available in Excel 2013. The modern FILTER workflow is separate. Microsoft’s performance guidance that explicitly covers Excel 2013 discusses INDEX, array formulas, helper ranges and SUMPRODUCT with Boolean coercion. Current Microsoft Support function pages generally list newer supported editions; they document the function behavior but are not native Excel 2013 test evidence.

Not tested in Excel. Documentation was checked October 16, 2026. The matching positions, selector results, duplicate retention, zero value and changed-quantity expectations were independently calculated outside Excel. The download is a CSV input fixture, not a tested interactive workbook. No native application screenshots are claimed.