Index Match All Partial Matches: How To
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This guide explains how to use Excel’s INDEX and MATCH functions to return all partial matches from a list, not just the first one. You enter a search string and a lookup range, and the formula spills out every row where the search text appears anywhere in the lookup column. The result is a dynamic array that updates automatically when your data changes.
Formula
The core formula uses FILTER with ISNUMBER(SEARCH()) to check each cell in the lookup range. In older Excel versions without dynamic arrays, you would use an array formula with INDEX, SMALL, and IF. The modern version is simpler:
=FILTER(list, ISNUMBER(SEARCH(search, list)))
Where:
- list – the range you want to search (e.g., A2:A100)
- search – the text you are looking for (e.g., “prod”)
- SEARCH – finds text anywhere in a cell, ignoring case
- ISNUMBER – converts the result to TRUE/FALSE
- FILTER – returns all rows where the condition is TRUE
If you need case-sensitive matching, replace SEARCH with FIND.
Worked Example
Assume your lookup range is A2:A100 and it contains product names like “Widget Pro”, “Basic Widget”, and “Pro Max”. You type “prod” in the search box. The formula evaluates each cell:
- “Widget Pro” contains “prod” → TRUE
- “Basic Widget” does not contain “prod” → FALSE
- “Pro Max” contains “prod” → TRUE
FILTER returns the two TRUE rows: “Widget Pro” and “Pro Max”. The result spills down automatically.
How to Use INDEX and MATCH for Partial Matches
Before dynamic arrays, the standard approach was an array formula. Here is the classic formula that returns the first match:
=INDEX(return_range, MATCH("*"&search&"*", lookup_range, 0))
To get all matches, you need to modify it with SMALL and IF to collect multiple row numbers. For example, in Excel 2019 and earlier, you would enter this as an array formula (Ctrl+Shift+Enter) and drag down:
=IFERROR(INDEX(return_range, SMALL(IF(ISNUMBER(SEARCH(search, lookup_range)), ROW(lookup_range)-MIN(ROW(lookup_range))+1), ROWS($A$1:A1))), "")
This works but requires manual filling and is harder to maintain. The FILTER version is cleaner and supported in Excel 365, Excel 2021, and Google Sheets.
Common Mistakes and How to Avoid Them
- Forgetting to lock ranges – Use absolute references ($A$2:$A$100) when dragging formulas.
- Using MATCH with wildcards incorrectly – The asterisk (*) matches any sequence, but it only works with text, not numbers.
- Case sensitivity – SEARCH ignores case; FIND is case-sensitive. Choose the right one for your data.
- Blank cells – FILTER will return blank rows if the range includes empty cells. Use a helper column to exclude them.
- Mixing data types – If your lookup column contains numbers, SEARCH may not work. Convert numbers to text with TEXT(cell, “0”).
How to Choose Between INDEX/MATCH, VLOOKUP, and FILTER
VLOOKUP can only return the first match and cannot search for partial text without wildcards. INDEX/MATCH is more flexible and faster, but it still returns only one match. FILTER is the best choice when you need all matches. Use these guidelines:
- Need the first match only → INDEX/MATCH or VLOOKUP
- Need all matches → FILTER (or array formula in older Excel)
- Need case-sensitive partial match → SEARCH with FIND
- Need to return multiple columns → FILTER with a range
Performance and Limitations
FILTER is efficient for moderate-sized data (up to tens of thousands of rows). For very large datasets, consider using Power Query or a database. The main limitation is that FILTER requires Excel 365 or Excel 2021. In older versions, the array formula can become slow and unwieldy.
Alternatives to INDEX/MATCH for Partial Matches
- Power Query – Use “Contains” filter in the UI; great for large data and no formulas.
- Excel Tables with slicers – Not partial matching, but you can filter manually.
- VBA macro – Write a custom function to loop through cells and collect matches.
- Google Sheets – FILTER works the same, and QUERY with “contains” is another option.
Frequently Asked Questions
Can I use wildcards with INDEX/MATCH for partial matches?
Yes, you can use “*” and “?” in the lookup value, but only when the lookup range contains text and the match_type is 0. For example, MATCH(“*”&A1&”*”, B:B, 0). However, this returns only the first match, not all.
How do I return all matches in Excel 2019 or earlier?
Use the array formula with SMALL and IF as shown above. Enter it with Ctrl+Shift+Enter and drag down. You must also wrap it in IFERROR to hide errors when no more matches exist.
Why does my FILTER formula return a #CALC! error?
This happens when no rows meet the condition. Wrap the formula in IFERROR, or use the IFNA function to return a custom message like “No matches found”.
Can I search across multiple columns for partial matches?
Yes, you can combine conditions with the + operator for OR logic. For example, FILTER(A2:C100, ISNUMBER(SEARCH(“prod”, A2:A100)) + ISNUMBER(SEARCH(“prod”, B2:B100))).
Is there a way to make the search case-insensitive?
SEARCH is always case-insensitive, so the FILTER formula using SEARCH is already case-insensitive. If you need case-sensitive, use FIND instead.
Related
Formula used
in search | Search text | - | prod | 0 in list | Lookup range (A2:A100) | - | A2:A100 | 0 out matches | Matching values | - | FILTER(list, ISNUMBER(SEARCH(search, list))) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.