INDEX and MATCH all partial matches

=FILTER(data,ISNUMBER(SEARCH(search,data))) To extract all matches based on a partial match, you can use a formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 is: =IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) where search (D5), CT (D8) and data (B5:B55) are named ranges. Note: in the current version of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER function.

April 14, 2025 · 1 min · 130 words · Amber Peterson

Table of Contents

The INDEX and MATCH formula explained here is meant forlegacy versions of Excelthat do not provide the FILTER function.

The second argument, 6, is an option to ignore errors.

The third argument is an expression that generates an array of matching results (described below).

Excel formula: Get relative row numbers in range

The clever bit is to divide the row numbers by the search results.

In a math operation like this, TRUE behaves like 1, and FALSE behaves like zero.

For a detailed explanation,see this page.

Excel formula: INDEX and MATCH all matches

it’s possible for you to use INDEX to retrieve individual values, or entire rows and columns.

SEARCH Function

The Excel SEARCH function returns the location of one text string inside another.

SEARCH returns the position offind_textinsidewithin_textas a number.

Excel formula: Get nth match with INDEX / MATCH

SEARCH supports wildcards, and isnotcase-sensitive….

How to look things up with INDEX

Excel formula: Cell contains specific text

Excel formula: Partial match with VLOOKUP

Excel formula: FILTER with partial match

Excel INDEX function

Excel AGGREGATE function

Excel ISNUMBER function

Excel SEARCH function