AI Excel Generator
← ALL GUIDES

How to Return Multiple Matches in Excel

Updated November 4, 2026 · AI Excel Generator

Excel can return every value that meets a condition, even when a lookup finds more than one matching row.

The right formula depends on your Excel version and whether you want results to spill into nearby cells, appear in one cell, or work in an older worksheet.

Return multiple matches with FILTER

In Microsoft 365, Excel 2021, and later versions that support dynamic arrays, FILTER is usually the simplest option.

It returns all rows from a range whose corresponding test is true, and the results spill into adjacent cells automatically.

Suppose product names are in A2:A10 and categories are in B2:B10.

To return every product in the category Fruit, enter:

=FILTER(A2:A10,B2:B10="Fruit","No matches")

If the matching rows contain Apple, Pear, and Orange, those three names appear in a vertical list starting in the formula cell.

The third argument supplies a message when no row matches.

Without it, FILTER returns a #CALC! error when the result is empty.

The include range must have the same number of rows as the array being filtered.

For example, A2:A10 and B2:B10 align; using B2:B9 creates a size mismatch.

Return matching rows instead of one column

To return several columns for each match, use the full data range as the first argument.

If product, category, and price are in A2:C10, with categories in column B, use:

=FILTER(A2:C10,B2:B10="Fruit","No matches")

Excel returns the complete matching rows across three columns.

This is useful for small reports where each result needs its associated details, such as an employee name, department, and start date.

Keep the cells where results will spill empty.

If another value blocks the output area, Excel displays #SPILL!.

Move or clear the blocking content, or place the formula in an area with enough room.

Match more than one condition

For rows that must satisfy both conditions, multiply the logical tests.

For example, to list products in the Fruit category with a price under 5, where prices are in C2:C10, enter:

=FILTER(A2:C10,(B2:B10="Fruit")*(C2:C10<5),"No matches")

Multiplication works like AND: both tests must be true for a row to be included.

To return rows that satisfy either of two category tests, add the tests instead:

=FILTER(A2:C10,(B2:B10="Fruit")+(B2:B10="Vegetable"),"No matches")

Addition works like OR: a row is included when at least one test is true.

Parentheses make it clear which conditions are being evaluated.

Return matches in one cell

To combine matching values into one cell rather than a spilled list, nest FILTER inside TEXTJOIN:

=TEXTJOIN(", ",TRUE,FILTER(A2:A10,B2:B10="Fruit",""))

The result might be Apple, Pear, Orange.

The first argument sets the separator, and TRUE tells TEXTJOIN to ignore empty values.

This is handy for compact summaries, but a long list can exceed Excel's cell character limit of 32,767 characters.

Return the nth match

Sometimes a worksheet needs only the first, second, or another numbered match.

In current Excel, INDEX and FILTER provide a clear way to select one item from the filtered list.

To return the second matching product:

=INDEX(FILTER(A2:A10,B2:B10="Fruit","No matches"),2)

This returns the second result in the filtered list, not the second row of the original range.

If fewer than two values match, the formula returns an error.

To show a message instead, wrap it in IFERROR:

=IFERROR(INDEX(FILTER(A2:A10,B2:B10="Fruit"),2),"Fewer than two matches")

Can older Excel versions return multiple matches?

Excel 2019 and earlier do not support the dynamic array behavior of FILTER.

A traditional array formula can return successive matching values, though it needs more setup.

In D2, enter the following formula and fill it down:

=IFERROR(INDEX($A$2:$A$10,AGGREGATE(15,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($B$2:$B$10="Fruit"),ROWS(D$2:D2))),"")

The formula returns the first match in D2, the second in D3, and so on.

AGGREGATE identifies the next matching position while ignoring errors from nonmatching rows.

This approach works in Excel 2010 and later.

In Excel 2007 or older, where AGGREGATE is unavailable, a legacy array formula using SMALL may be needed; those versions typically require confirming array formulas with Ctrl+Shift+Enter.

For older installations, test a formula in a copy of the workbook first.

Formula support varies by version, and legacy array formulas are harder to maintain than FILTER.

Common problems and edge cases

  • No matches: Provide [if_empty] in FILTER, such as "No matches", to avoid a #CALC! result.
  • Blocked spill range: Clear cells in the output area if Excel shows #SPILL!.
  • Text with extra spaces: A cell containing Fruit may not match Fruit.

    Clean source data with TRIM if leading or trailing spaces are the cause.
  • Case sensitivity: Standard equality tests such as B2:B10="Fruit" are not case-sensitive.

    For case-sensitive matching, use EXACT inside FILTER, for example =FILTER(A2:A10,EXACT(B2:B10,"Fruit"),"No matches").
  • Data added later: Fixed ranges like A2:A10 do not expand when new rows are added.

    Convert the data to an Excel Table and use structured references, or adjust the ranges.
  • Different regional separators: Some Excel settings use semicolons instead of commas between function arguments.

    If Excel rejects an otherwise valid formula, try replacing argument commas with semicolons.

For a growing list, an Excel Table keeps the formula ranges aligned as records are added.

For a quick lookup on a current Microsoft 365 or Excel 2021 workbook, start with FILTER; for one-cell summaries, combine it with TEXTJOIN; and for older versions, use a fill-down formula that retrieves matches one at a time.