Formula Errors

How to Fix INDEX MATCH with LARGE Returning Zero in Excel

John Wilson Oct 21, 2026 869 views

Question details

An INDEX and MATCH formula accurately retrieves results when paired with the SMALL function, but returns an incorrect zero value when the formula is modified to use the LARGE function.

How to Fix INDEX MATCH with LARGE Returning Zero in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to look up and retrieve information based on the Nth largest value in a dataset using the INDEX, MATCH, and LARGE functions combined.
Observed behavior: The formula evaluates to zero instead of retrieving the expected corresponding value, often caused by empty cells being interpreted as zero or the use of entire-column references.

Before you start

Before modifying your formula, quickly inspect your dataset to ensure there are no hidden blank cells or text values formatted as numbers, as these can easily skew the results of the LARGE function.

Solution 1Recommended

Use Bounded Ranges Instead of Entire-Column References

Replacing entire-column references (like A:A) with bounded ranges prevents the spreadsheet from evaluating millions of empty rows at the bottom of the sheet as zeros.

When an entire column is referenced, functions often process the blank cells below your actual data. Because Excel evaluates empty cells as zero, a MATCH function looking for a specific value might incorrectly match the first blank cell it encounters if the lookup logic falls back to zero.

Locate the formula

Select the cell containing the INDEX MATCH formula that is returning zero.

Identify entire-column references

Check the formula bar for any unbounded references, such as A:A or AH:AH.

Apply bounded ranges

Change the unbounded columns to specific cell ranges that perfectly match your data size. For example, change AH:AH to AH2:AH20000.

Update the formula

Re-enter the formula with absolute references to lock the range. For example: =INDEX($U2:$U20000,MATCH(LARGE($AH2:$AH20000,$B105),$AH2:$AH20000,0)) and press Enter.

Use Bounded Ranges Instead of Entire-Column References

Performance Boost: Using bounded ranges not only fixes the zero-return error but also significantly improves spreadsheet calculation speeds.

Solution 2

Convert Your Data to a Structured Table

Formatting your raw data as a structured table automatically manages range boundaries, ensuring formulas only evaluate cells that contain actual data.

Solution 3

Check for Data Type Mismatches and Blank Cells

Verify that your lookup array does not contain actual zero values or text-formatted numbers that the LARGE function cannot process properly.

Advanced Spreadsheet Solutions

Use WPS Spreadsheet to Master Complex Formulas

WPS Office provides a highly compatible, robust spreadsheet application that flawlessly executes advanced functions like INDEX, MATCH, and LARGE. Enjoy an optimized calculation engine that prevents common structural errors and handles massive datasets with ease.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the raw data.
2. Select the target cell: Click the cell where you want to output the Nth largest matched value.
3. Input the bounded formula: Type your formula using specific range sizes, such as =INDEX($A$2:$A$100, MATCH(LARGE($B$2:$B$100, 1), $B$2:$B$100, 0)).
4. Evaluate the formula: Press Enter to instantly calculate the exact value without zero-return errors.

Fully compatible with Microsoft Excel formulas and functionsBuilt-in Error Checking features to easily debug complex formulasLightweight application ensures high performance on large data rangesFree to use with intuitive structured table creation tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does INDEX MATCH work with SMALL but fail with LARGE?

When using entire-column references, millions of empty cells are included in the array. Excel treats empty cells as zero. The SMALL function might ignore these if your actual values are greater than zero or negative, but LARGE can behave unpredictably when evaluating arrays padded with millions of zeros, causing the MATCH function to grab the first blank cell it sees.

How do I deal with duplicate values when using LARGE in an INDEX MATCH?

The MATCH function always returns the position of the first occurrence it finds. If your dataset has duplicate Nth largest values, a standard INDEX MATCH will only return the lookup result for the first one. To fix this, you either need to use complex array formulas, the newer FILTER function, or create a helper column that adds a tiny unique decimal to each number to break ties.

Are there modern alternatives to using INDEX MATCH with LARGE?

Yes. If you are using updated spreadsheet software, you can use the SORT or SORTBY functions combined with INDEX, or use the dynamic FILTER function to retrieve corresponding data much more easily without nesting MATCH and LARGE.

Excel & Data Problems