Index and Match All Matches: Full Guide
- Number of matches to return
- 3
- You entered
- Data rows
- 10
- You entered
This guide explains how to use Excel’s INDEX and MATCH functions to return all matching values from a list, not just the first one. You’ll learn the standard formula, how to adapt it with array formulas or dynamic arrays, and how to handle common issues like duplicates and blanks.
Formula
The core formula for returning all matches uses an array formula that increments the match position. In older Excel versions, you enter it with Ctrl+Shift+Enter:
=INDEX(range, SMALL(IF(condition, ROW(range)-MIN(ROW(range))+1), ROW(A1)))
In Excel 365 and Excel 2021, the same logic works without CSE because dynamic arrays spill results automatically.
Variables:
- range – the column containing the values you want to return.
- condition – the logical test that identifies matches (e.g.,
$A$2:$A$100=$E$1). - ROW(range)-MIN(ROW(range))+1 – converts row numbers to relative positions (1,2,3…).
- ROW(A1) – acts as a counter; when dragged down, becomes 1,2,3…
Worked Example
Suppose you have a list of products in A2:A11 and you want all rows where the product is “Widget”. Your lookup value is in E1.
- Enter the formula in C2:
=INDEX($B$2:$B$11, SMALL(IF($A$2:$A$11=$E$1, ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1), ROW(A1))) - Press Ctrl+Shift+Enter in classic Excel, or just Enter in Excel 365.
- Drag down until you get errors (or let dynamic arrays spill).
With the default values (n=3, data rows=10), the formula returns the first three matching values from column B. If fewer than 3 matches exist, you’ll see #NUM! errors.
How It Works
The trick is combining INDEX with SMALL and IF. The IF function creates an array of relative positions for rows that meet the condition, and FALSE for others. SMALL then extracts the k-th smallest position, which INDEX uses to fetch the corresponding value. The ROW(A1) counter increments as you fill down, so you get the 1st match, then 2nd, then 3rd, and so on.
In Excel 365, you can simplify with the FILTER function: =FILTER(B2:B100, A2:A100=E1) returns all matches directly. But INDEX/MATCH remains essential for older versions and for cases where you need more control.
How to Choose the Right Approach
- Excel 365 / 2021 – use FILTER for simplicity, or INDEX/MATCH with dynamic arrays if you need to nest the result in another formula.
- Excel 2019 and earlier – use the array formula with Ctrl+Shift+Enter.
- Google Sheets – use FILTER or the same array formula (no CSE needed).
Common Problems and Fixes
- #NUM! errors – you dragged the formula beyond the number of matches. Use IFERROR to display blanks.
- Wrong results because of blank cells – add a condition to exclude blanks:
IF((A2:A100=E1)*(B2:B100<>""), ...) - Duplicates – the formula returns duplicates if they exist in the source. To get unique matches, wrap with UNIQUE (Excel 365) or use a more complex array formula.
- Case sensitivity – standard MATCH is case-insensitive. Use EXACT in the condition for case-sensitive matching.
Comparisons with VLOOKUP and FILTER
VLOOKUP only returns the first match, and it requires the lookup column to be the leftmost. INDEX/MATCH is more flexible and can return all matches with the array trick. FILTER is the modern replacement, but it’s not available in older versions. INDEX/MATCH also works in Google Sheets and LibreOffice Calc.
Costs and Alternatives
There is no cost – these are built-in Excel functions. Alternatives include FILTER (Excel 365), the LOOKUP function (first match only), and using a pivot table to list all matching items. For large datasets, consider using Power Query to filter rows, which is more efficient.
Safety and Accuracy Tips
- Always lock ranges with absolute references ($) when copying formulas.
- Ensure your data has no merged cells or hidden rows that could affect the row numbers.
- Test with a small dataset before applying to thousands of rows.
- Remember that array formulas can slow down large workbooks – use them judiciously.
Frequently Asked Questions
Can INDEX/MATCH return multiple matches without array formulas?
In Excel 365, yes – dynamic arrays handle it automatically. In older versions, you must use Ctrl+Shift+Enter to enter the array formula. There is no non-array way to get all matches in classic Excel.
How do I return all matches in a single cell?
Use TEXTJOIN with the array formula: =TEXTJOIN(", ", TRUE, IF(condition, range, "")) entered as an array formula. In Excel 365, you can use FILTER inside TEXTJOIN.
Why does my formula return #NUM! when there are matches?
This usually happens when you drag the formula down too far, or when the condition uses relative references that change. Check that your ranges are absolute and that you haven’t exceeded the number of matches.
Can I use INDEX/MATCH to return matches from a different sheet?
Yes, just reference the other sheet in the range, e.g., Sheet2!$B$2:$B$100. The formula works the same way.
Is FILTER better than INDEX/MATCH for all matches?
FILTER is simpler and faster in Excel 365. However, INDEX/MATCH is still useful when you need to return matches in a specific order, or when you’re working in older versions.