Excel INDEX MATCH Formula for Partial Text Search

Managing and extracting specific records from massive, unstructured Excel sheets is a persistent challenge for data analysts. When reconciling diverse financial portfolios, such as tracking standard funding sources, manual data curation becomes highly inefficient. Implementing an Index-Match formula with partial text search grants users the ability to instantly isolate and retrieve non-exact entries.

As a key stipulation, this advanced method requires precise array handling and the integration of SEARCH and ISNUMBER functions to prevent formula errors. For example, searching "Gov" can dynamically extract rows containing "Government Grant." Below, we break down the exact formula syntax to streamline your lookup workflow.

Excel is an incredibly powerful tool for data analysis, but retrieving data based on partial text matches can quickly become complicated. Standard lookup functions like VLOOKUP or basic INDEX/MATCH are designed to find the first exact match. When you need to perform a partial text search (such as finding all instances containing "phone" within "Smartphones," "Headphones," and "Mobile Phones") and extract entire matching rows, you must look beyond basic functions.

Depending on your version of Excel, you have two primary methodologies: the modern, dynamic array-based approach using FILTER, or the classic array formula approach using INDEX, SMALL, IF, ROW, and ISNUMBER/SEARCH. In this comprehensive guide, we will explore both methods step-by-step, explaining how they work and how to implement them in your spreadsheets.

The Dataset

To illustrate these techniques, we will use the following sample inventory dataset (spanning cells A1:C8):

Our goal is to create a search tool where typing a partial keyword (e.g., "phone") into a search cell (e.g., E2) instantly extracts and indexes all matching rows from our dataset.

Method 1: The Modern Solution (Excel 365 & Excel 2021+)

If you are using a modern version of Excel, you have access to dynamic arrays and the FILTER function. This makes extracting multiple matching rows remarkably simple, fast, and elegant.

The Formula

=FILTER(A2:C8, ISNUMBER(SEARCH(E2, B2:B8)), "No Matches Found")

How It Works

This formula nests several functions to locate the matches and filter the array:

• SEARCH(E2, B2:B8): This function looks for the substring in cell E2 (e.g., "phone") within the range B2:B8. If it finds the text, it returns the starting character position of the match as a number. If it doesn't find the text, it returns a #VALUE! error. It is case-insensitive.
• ISNUMBER(...): This converts the array of numbers and error values generated by the SEARCH function into an array of TRUE and FALSE values. Any cell containing the search term becomes TRUE, while non-matching cells become FALSE.
• FILTER(A2:C8, ...): The FILTER function evaluates the TRUE/FALSE array and returns only the rows from our source range (A2:C8) that correspond to a TRUE value. If no rows match, it returns the text defined in the third argument: "No Matches Found".

Because this is a dynamic array formula, you only need to type it into a single cell. The results will automatically "spill" down and across into the adjacent cells to display all matching records.

Method 2: The Classic Solution (Excel 2019 and Older)

If you are working on an older version of Excel that does not support dynamic arrays, you cannot use the FILTER function. Instead, you must build a robust array formula using the classic combination of INDEX, SMALL, IF, ROW, and ISNUMBER/SEARCH.

Because older Excel versions do not "spill" results, you must enter this formula in the top-left cell of your destination range, press Ctrl + Shift + Enter (CSE) to write it as an array formula, and then drag it down and across to fill your search table.

The Formula

Enter the following formula in your first destination cell (for example, G2, to pull the Product ID):

{=IFERROR(INDEX(A$2:A$8, SMALL(IF(ISNUMBER(SEARCH($E$2, $B$2:$B$8)), ROW($B$2:$B$8) - ROW($B$2) + 1), ROW(1:1))), "")}

Note: Do not type the curly braces { } manually. Excel will add them automatically when you press Ctrl + Shift + Enter.

How It Works

This formula performs some complex logical gymnastics to extract the correct rows. Let's break it down from the inside out:

• ISNUMBER(SEARCH($E$2, $B$2:$B$8)): Just like in the modern formula, this generates an array of TRUE and FALSE values depending on whether the cell contains our search term.
• ROW($B$2:$B$8) - ROW($B$2) + 1: This portion generates an array of relative row numbers. For our range of 7 rows, it generates {1; 2; 3; 4; 5; 6; 7}.
• IF(..., ROW(...)): The IF statement combines the two arrays. If a cell matches our search term (returns TRUE), it keeps its relative row number. If it is FALSE, it is discarded. For our search term "phone", this outputs an array like {1; FALSE; 3; FALSE; 5; FALSE; FALSE}.
• SMALL(..., ROW(1:1)): The SMALL function returns the k-th smallest value in a data set. The ROW(1:1) argument serves as our counter k, starting at 1. When you copy the formula down to the next row, ROW(1:1) changes to ROW(2:2), which evaluates to 2, then 3, and so on. In our first row, it extracts the 1st smallest number (which is 1). In the second row, it extracts the 2nd smallest number (which is 3).
• INDEX(A$2:A$8, ...): Now that we have the relative row indices (1, 3, 5), INDEX goes to range A$2:A$8 and retrieves the values located at those relative positions.
• IFERROR(..., ""): Once all matching values have been returned, subsequent rows will return a #NUM! error because the SMALL function runs out of numbers. The IFERROR wrapper catches these errors and displays an empty string ("") instead, leaving those cells clean and blank.

Case-Sensitivity: SEARCH vs. FIND

Both of the methods outlined above rely on the SEARCH function, which is case-insensitive. Searching for "phone" will return matches containing "phone", "Phone", or "PHONE".

If you require a case-sensitive search-where searching for "phone" matches "Headphones" but completely ignores "Smart Phone Pro"-simply replace the SEARCH function with the FIND function in either formula:

=FILTER(A2:C8, ISNUMBER(FIND(E2, B2:B8)), "No Matches Found")

Multi-Criteria Partial Search

What if you want to filter matching rows based on multiple partial-text criteria? For example, you want to find items that contain "Phone" in the Product Name and "Electronics" in the Department column.

With modern Excel, you can easily combine criteria using boolean logic. In Excel formulas, multiplying arrays acts as an AND operator:

=FILTER(A2:C8, ISNUMBER(SEARCH(E2, B2:B8)) * ISNUMBER(SEARCH(F2, C2:C8)), "No Matches")

In this scenario, E2 contains the product keyword (e.g., "phone") and F2 contains the department keyword (e.g., "Electronics"). Only rows meeting both conditions will be outputted.

Summary of Best Practices

• Use Tables: Convert your source data range to an official Excel Table (press Ctrl + T). This allows your formulas to use structured references (like Table1[Product Name]) that automatically expand when you add new data.
• Lock Your References: If you are using the classic INDEX/SMALL array formula, ensure you use absolute references (using the $ sign, e.g., $B$2:$B$8) to lock the ranges before dragging the formula across and down.
• Upgrade When Possible: The FILTER function is vastly superior in performance, ease of use, and auditability compared to legacy CSE array formulas. If your organization's version of Excel supports it, deprecate your legacy array formulas in favor of dynamic arrays.