INDEX MATCH Return Multiple Results in Google Sheets

Standard INDEX MATCH returns one value — the first row that matches your search key. When multiple rows match and you need all of them, you need a different approach. This guide covers the classic SMALL/IF array formula for returning the Nth match, then shows the modern FILTER function alternative that handles multiple results in a single formula.

In This Guide

• Why INDEX MATCH Returns One Result
• The SMALL/IF Array Pattern
• Return Multiple Results: Step-by-Step
• The FILTER Alternative (Recommended)
• When to Use Each Approach
• Tips
• Related Google Sheets Tutorials
• Frequently Asked Questions

Why INDEX MATCH Returns One Result

MATCH searches a range and returns the position of the first match it finds. If your data has multiple rows with the same value, MATCH stops at the first one.

Using the product inventory, two products are in the Surveillance category: Field Binoculars (row 5) and Evidence Bag Kit (row 8).

=INDEX(B2:B9, MATCH("Surveillance", C2:C9, 0))

This returns Field Binoculars because it appears first. The Evidence Bag Kit is ignored.

To get both results, you need a formula that finds the 1st match, the 2nd match, and so on.

The SMALL/IF Array Pattern

The classic approach uses SMALL and IF inside INDEX to return each match by position number.

=INDEX(return_range, SMALL(IF(criteria_range=criteria, ROW(criteria_range)-ROW(first_cell)+1), N))

Here is how the parts work:

1. IF(criteria_range=criteria, …) — creates an array of row positions where the condition is true, and FALSE everywhere else
2. ROW(criteria_range)-ROW(first_cell)+1 — converts sheet row numbers into relative positions within the range (1, 2, 3…)
3. SMALL(…, N) — returns the Nth smallest position number. N=1 gives the first match, N=2 gives the second
4. INDEX(return_range, …) — returns the value at that position

Return Multiple Results: Step-by-Step

We’ll find all Surveillance products from the product inventory.

Sample Data

The inventory has 8 products. Two are in the Surveillance category: Field Binoculars (SKU-104, row 5) and Evidence Bag Kit (SKU-107, row 8).

The product inventory runs from A1:F9. You want to return all Product Names (column B) where the Category (column C) is “Surveillance”.

=INDEX(B2:B9, MATCH("Surveillance", C2:C9, 0))

This returns Field Binoculars only. MATCH found the first “Surveillance” at position 3 in C2:C9 and stopped.

To get the first Surveillance product:

=INDEX(B2:B9, SMALL(IF(C2:C9="Surveillance", ROW(C2:C9)-ROW(C2)+1), 1))

This returns Field Binoculars (the 1st match).

To get the second Surveillance product, change the last number to 2:

=INDEX(B2:B9, SMALL(IF(C2:C9="Surveillance", ROW(C2:C9)-ROW(C2)+1), 2))

This returns Evidence Bag Kit (the 2nd match).

Each formula occupies one cell. To return all matches, you need a separate formula for each result row, incrementing the N value (1, 2, 3…).

The FILTER function returns all matching rows at once:

=FILTER(B2:B9, C2:C9="Surveillance")

This returns both Field Binoculars and Evidence Bag Kit in a single formula. The results spill into adjacent cells below automatically.

No array entry needed. No incrementing N values. One formula handles everything.

The FILTER Alternative (Recommended)

For returning multiple matching rows, FILTER is the preferred approach in current Google Sheets.

Return multiple columns

FILTER can return entire rows, not just one column:

=FILTER(A2:F9, C2:C9="Surveillance")

This returns both Surveillance rows with all 6 columns (Product ID, Name, Category, Price, Stock, Supplier).

Multiple conditions

Add conditions by including more criteria arrays:

=FILTER(B2:B9, C2:C9="Surveillance", E2:E9>100)

This returns only Surveillance products with stock above 100. The only match is Evidence Bag Kit (500 in stock). Field Binoculars has 50 in stock and is excluded.

Handle no results

When no rows match, FILTER returns #N/A. Use IFERROR to show a custom message:

=IFERROR(FILTER(B2:B9, C2:C9="Books", E2:E9>500), "No matches found")

When to Use Each Approach

FILTER handles the majority of “return multiple results” scenarios. Use the SMALL/IF pattern when you need specific control over which match number to return.

Tips

1. FILTER is the first choice for multiple results. It is shorter, self-expanding, and does not need Ctrl+Shift+Enter. Use it unless you have a specific reason for the SMALL/IF pattern.

2. Leave room for FILTER results to spill. FILTER outputs expand downward. If cells below the formula contain data, you get a #REF! error. Keep the area below the formula empty.

3. Use IFERROR with both approaches. SMALL/IF returns #NUM! when N exceeds the match count. FILTER returns #N/A when no rows match. Wrap both in IFERROR for clean output.

4. Combine FILTER with SORT for ordered results. =SORT(FILTER(B2:B9, C2:C9="Surveillance")) returns matching products in alphabetical order.

5. For returning one value per row across a column, use ARRAYFORMULA with INDEX MATCH. See INDEX MATCH with ARRAYFORMULA for that pattern.

Related Google Sheets Tutorials

• INDEX MATCH: The Complete Guide — Full syntax, left lookups, and the combined formula pattern
• INDEX MATCH with Multiple Criteria — Match on two or more conditions
• INDEX MATCH with ARRAYFORMULA — Apply INDEX MATCH across an entire column
• FILTER Function: Complete Guide — Return multiple matching rows with conditions
• ARRAYFORMULA: Complete Guide — Apply formulas to entire columns at once

Frequently Asked Questions

Can INDEX MATCH return more than one result?

Standard INDEX MATCH returns one value — the first match. To return multiple results, use the SMALL/IF array pattern or the FILTER function, which is the modern and easier approach.

What is the difference between INDEX MATCH and FILTER for multiple results?

INDEX MATCH with SMALL/IF returns results one per row using an array formula. FILTER returns all matching rows at once in a single formula that spills into adjacent cells. FILTER is shorter and easier to maintain.

Why does my INDEX MATCH only return the first match?

MATCH always returns the position of the first match it finds. To return all matches, you need an array formula that generates a sequence of match positions (using SMALL and IF) or switch to the FILTER function.

Does FILTER replace INDEX MATCH for multiple results?

For most cases, yes. FILTER is the recommended approach for returning multiple matching rows. INDEX MATCH with SMALL/IF is still useful when you need only the Nth match or when combining with other array logic.

How do I return the 2nd or 3rd match with INDEX MATCH?

Use the SMALL/IF pattern: =INDEX(range, SMALL(IF(criteria, ROW(range)-ROW(first_cell)+1), N)) where N is the match number. N=1 returns the first match, N=2 returns the second, and so on.