Function Problems

How to Find the Nth Matching Value in Excel Using INDEX and FILTER

Natalie Taylor Oct 9, 2026 870 views

Question details

The user needs to extract multiple matching values (first, second, third, etc.) from a dataset based on a partial text search.

How to Find the Nth Matching Value in Excel With INDEX and FILTER

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for multiple occurrences of a specific item or partial text string across a dataset and returning corresponding values from other columns.
Observed behavior: Standard VLOOKUP only returns the first match. The user needs to retrieve all matching values or specific Nth matches based on partial lookup text like 'Merce' or 'Audi'.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365, Excel 2021, or later) as the dynamic array FILTER function is not supported in older versions.

Solution 1Recommended

Use FILTER and SEARCH (Recommended for Modern Excel)

The most efficient way to return multiple occurrences based on a partial match utilizing dynamic arrays.

Modern Excel supports dynamic arrays, allowing a single formula to spill multiple results across adjacent cells. By combining the FILTER function with ISNUMBER and SEARCH, you can easily pull every matching record for a specific partial text query.

Select the destination cell

Click on an empty cell where you want the matching results to begin spilling. Ensure there is enough empty space to the right or below the cell to avoid a #SPILL! error.

Enter the FILTER and SEARCH formula

Type the formula: =FILTER($H$3:$H$7, ISNUMBER(SEARCH(A6, $G$3:$G$7))). In this formula, A6 contains your search text (e.g., 'Merce'), G3:G7 is the column being searched, and H3:H7 is the column containing the values you want to return.

Transpose the results (Optional)

If you need the first, second, and third matches to appear in a single row rather than a column, wrap your formula in the TRANSPOSE function: =TRANSPOSE(FILTER($H$3:$H$7, ISNUMBER(SEARCH(A6, $G$3:$G$7)))).

Execute the formula

Press Enter. All matches will automatically populate the adjacent cells, giving you the 1st, 2nd, 3rd, and subsequent matching values.

Use FILTER and SEARCH (Recommended for Modern Excel)

Dynamic Spilling: Because FILTER is a dynamic array function, it automatically expands to return all matches, even if there are more than three occurrences.

Solution 2

Use INDEX and SMALL Array Formula (For Older Excel Versions)

Use this legacy method if you are using older versions of Excel that do not support the FILTER function.

Powerful Spreadsheet Tool

Easily Find Nth Matching Values in WPS Spreadsheet

WPS Office Spreadsheet fully supports modern array functions like FILTER and INDEX. You can seamlessly execute complex lookup queries, handle partial text searches, and manipulate large datasets just like in Microsoft Excel.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing your lookup data.
2. Enter the FILTER formula: Select your target cell and input =TRANSPOSE(FILTER(Return_Range, ISNUMBER(SEARCH(Search_Cell, Lookup_Range)))).
3. Get instant results: Press Enter to execute the formula and instantly extract all Nth matching values across your cells.

Fully compatible with Microsoft Excel formulas including FILTER, INDEX, and SEARCH.Free, lightweight, and easy to use for all your data analysis needs.Built-in dynamic arrays support for seamless data extraction.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

A #CALC! error occurs when the FILTER function cannot find any matches. You can handle this gracefully by adding a third argument to your function for 'if_empty'. For example: =FILTER(H3:H7, ISNUMBER(SEARCH(A6, G3:G7)), "No Match Found").

How do I return a specific Nth match instead of all matches?

To isolate a specific Nth value from the FILTER array, wrap the formula in the INDEX function. For example, =INDEX(FILTER(H3:H7, ISNUMBER(SEARCH(A6, G3:G7))), 2) will return exactly the second match.

Does the SEARCH function distinguish between uppercase and lowercase?

No, the SEARCH function is case-insensitive. If you require a strict case-sensitive partial match, use the FIND function instead of SEARCH in your formula.

Can I extract data from multiple columns at once with FILTER?

Yes. If you select a multi-column range as your 'return_array' in the FILTER function (e.g., A3:D7 instead of just H3:H7), the formula will spill the corresponding rows across multiple columns.

Excel & Data Problems