Formula Errors

How to Fix INDEX and MATCH Errors with Non-Contiguous Ranges in Excel

Muhammad Talha Oct 1, 2026 868 views

Question details

The user needs to extract data from non-contiguous named ranges that include subgroup totals and duplicate project values, but traditional lookup functions are failing.

How to Fix INDEX and MATCH Errors with Non-Contiguous Ranges

Product: Excel
Device & OS: not provided
Scenario: Attempting to look up and return project names based on their values across a dataset interrupted by subgroup total rows, which involves potential duplicate values.
Observed behavior: Using INDEX and MATCH directly on non-contiguous ranges returns an #N/A error, and the MATCH function only returns the first occurrence of a duplicate value rather than all matching projects.

Before you start

Before modifying your formulas, ensure your dataset has a consistent layout and identify a clear condition or helper column that can distinguish valid project rows from subgroup total rows.

Solution 1Recommended

Use the FILTER Function to Handle Non-Contiguous Ranges and Duplicates

Replace INDEX and MATCH with the dynamic FILTER function, which can seamlessly exclude subgroup totals and return multiple results for duplicate values.

INDEX and MATCH functions are not designed to naturally process non-contiguous ranges or return multiple results for duplicate values. By using the FILTER function, you can dynamically exclude total rows and retrieve every matching item simultaneously.

Identify valid data rows

Determine the logical condition that identifies actual project rows while excluding subgroup totals (for example, checking if a specific column does not contain the word 'Total').

Apply the FILTER formula

In your target cell, type the formula combining FILTER and LARGE to return matching projects. For example: =FILTER(Projects, (ConditionToExcludeTotals) * (AllProjectsSum=LARGE(AllProjectsSum, 1))).

Allow the results to spill

Press Enter. Unlike MATCH, which stops at the first duplicate, the FILTER formula will automatically spill all projects that share the specified target value into adjacent cells.

Use the FILTER Function to Handle Non-Contiguous Ranges and Duplicates

Dynamic Array Support: The FILTER function requires a spreadsheet version that supports dynamic arrays (such as Microsoft 365, Excel 2021, or recent versions of WPS Office). It drastically simplifies complex lookups.

Solution 2

Filter the Arrays Before Applying INDEX and MATCH

If you must use INDEX and MATCH for a specific extraction, apply FILTER first to create a contiguous virtual array without subgroup totals.

Easily Manage Complex Array Formulas in WPS Spreadsheet

WPS Office fully supports advanced dynamic arrays and lookup functions like FILTER, INDEX, and MATCH, allowing you to seamlessly process non-contiguous ranges and complex datasets without dealing with legacy formula limitations.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your non-contiguous data ranges.
2. Enter the FILTER formula: Click on the target output cell and input your =FILTER(...) formula to efficiently exclude total rows and define your criteria.
3. Spill the matching results: Press Enter. WPS Spreadsheet will instantly calculate and spill all duplicate matches into the adjacent cells perfectly.

Fully compatible with Microsoft Excel formulas and array functionsBuilt-in dynamic array support for functions like FILTER and SORTLightweight, fast, and free to use for complex daily data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does the MATCH function only return one value when there are duplicates?

By design, the MATCH function searches sequentially from top to bottom (or left to right) and completely stops calculating at the first exact match it finds. To return multiple matches for duplicate values, you must use dynamic array functions like FILTER.

Can INDEX and MATCH work with multiple non-contiguous ranges directly?

While the INDEX function can accept multiple area references (by enclosing the ranges in an extra set of parentheses), the MATCH function cannot look up values across non-contiguous ranges natively. You must consolidate the data first using functions like FILTER or VSTACK.

How do I systematically exclude subtotal rows from my formula calculations?

The most reliable method is to add a helper column that flags subtotal rows (for example, marking them with an 'X' or leaving them blank). You can then reference this helper column as a logical criteria array inside your FILTER formula to automatically exclude those specific rows.

Excel & Data Problems