ThisCalculator

Everyday

Index Match All Partial Matches: How To

Updated · Standard formula, shown below

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:

  1. “Widget Pro” contains “prod” → TRUE
  2. “Basic Widget” does not contain “prod” → FALSE
  3. “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.