Fixing Excel INDEX MATCH That Returns the Wrong Row When Data Is Sorted

You built an INDEX MATCH formula that worked perfectly, then someone sorted the data — and now it silently returns the wrong result. No error, no warning, just a plausible-looking number that happens to be wrong. That's the worst kind of bug.

The root cause is almost always the third argument to MATCH, the one most tutorials skip over. This guide explains exactly what it does, why sorting triggers the problem, and how to lock it down so sorting never breaks your lookups again.

What You'll Learn

• How the match_type argument controls which row MATCH returns
• Why sorted data makes approximate-match mode produce silently wrong results
• The one-character fix that makes INDEX MATCH bulletproof against sorting
• When approximate match is intentional and how to handle it safely
• How to audit existing formulas to spot this bug before it costs you

The Problem With INDEX MATCH on Sorted Data

Consider a product table: column A holds product IDs, column B holds prices. You write an INDEX MATCH to pull the price for a given ID. It works for weeks. Then a colleague sorts the table alphabetically by product name, and your lookups start returning prices for completely different products.

The scary part is that Excel doesn't tell you anything is wrong. The formula returns a value, it just isn't the value you asked for. If you're not spot-checking results, this kind of error can propagate through an entire report unnoticed.

How INDEX MATCH Actually Works

MATCH finds the position of a value within a range and returns a number. INDEX uses that number to retrieve the item at that position from another range. The classic pattern looks like this:

=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))

For example, to find the price of product ID "P-042" in a table where column A has IDs and column B has prices:

=INDEX(B2:B100, MATCH("P-042", A2:A100, 0))

The MATCH part scans column A, finds the row where the value equals "P-042", and returns that row number. INDEX then retrieves the value at that position in column B. Straightforward — until match_type is wrong.

The Real Culprit: match_type

MATCH accepts three values for match_type, and they behave very differently:

The default is 1 — approximate match ascending. If you omit the third argument entirely, Excel assumes you want approximate match. Many formulas you find in tutorials or inherited spreadsheets omit it, which makes them time bombs for sorted datasets.

What Happens When match_type Is 1 or -1

With match_type = 1, Excel uses a binary search algorithm. It jumps to the middle of the range, checks whether the value is above or below, then narrows down. This is fast on large datasets, but it only produces a correct result when the data is sorted in ascending order.

If the data isn't sorted ascending, the binary search may stop at the wrong position because it assumes it has gone past the target when it hasn't. The result is a row that happens to sit near where a sorted result would be, not the row where your actual lookup value lives.

Here's a concrete example. Suppose your product IDs in A2:A10 are: P-010, P-030, P-020, P-005. With match_type = 1 and lookup value "P-020", Excel's binary search may land on P-030's row because it sees a value higher than P-020 and concludes it went too far, then settles on P-010's row. It never finds P-020 at all, and it doesn't error — it just returns the nearest lower value it encountered.

This is also why the bug only appears after sorting. Before sorting, if the data happened to be in insert order, approximate match might accidentally produce the right answer. After a sort, the row positions shift and the lucky accident stops working.

How to Fix It: Force Exact Match

The fix is simple: always supply 0 as the third argument to MATCH unless you have a deliberate reason for approximate match.

=INDEX(B2:B100, MATCH("P-042", A2:A100, 0))

With match_type = 0, Excel performs a linear scan through the entire lookup range looking for the first cell that equals your lookup value exactly. It doesn't care what order the data is in. Sorting, filtering, and rearranging rows will never affect the result.

If you have a large spreadsheet full of inherited INDEX MATCH formulas, you can find all occurrences that omit the argument or use 1 with a simple Find & Replace. Open Find & Replace (Ctrl+H), check Match entire cell contents off, search for MATCH(, and review each hit in the formula bar. Look for MATCH(something, range) with only two arguments, or MATCH(something, range, 1). Change either pattern to add or correct the third argument to 0.

You can also audit formulas programmatically if the workbook is large. The following approach in the Excel Name Manager or a quick VBA scan helps you spot omitted arguments across many sheets — but even a manual pass through the formula bar on each key cell is usually faster than you'd expect.

When You Actually Want Approximate Match

Approximate match isn't a bug in itself. It's the right tool for range-based lookups, like tax brackets, shipping tiers, or grade thresholds — anywhere you want "the row where the value falls within a range."

For example, if you have a commission table where sales up to 10,000 earn 5%, up to 50,000 earn 8%, and above that earn 10%, approximate match with a sorted table is exactly what you want:

=INDEX(C2:C4, MATCH(B2, A2:A4, 1))

Here, match_type = 1 finds the largest threshold that doesn't exceed the sales amount. This only works correctly when column A is sorted ascending — which it should be for a threshold table you control.

The rule of thumb: use 0 for ID-based lookups where you want a specific row. Use 1 or -1 only for threshold tables you own and keep sorted. Never use approximate match on data that users can reorder.

Common Variations of This Bug

Omitted match_type in a formula copied from online

A huge proportion of INDEX MATCH examples on the internet omit the third argument. They work in the author's sample file, which happens to be in a compatible order. When you paste that formula into your own workbook, it may work until the data changes order.

Always check: does the MATCH inside any formula you borrow from outside explicitly include , 0) at the end?

Table sorted after formula was written

This is the classic scenario. You write the formula, test it on unsorted data, and everything looks fine. A colleague sorts the table by a different column to make it easier to read, and the lookup silently breaks. Adding the 0 argument before this happens is the only reliable protection.

For background on how data-order issues affect other lookup functions, the article on fixing VLOOKUP #N/A errors caused by trailing spaces covers similar traps where the lookup range doesn't behave as expected.

match_type = -1 on ascending data

Less common but equally silent. If someone set match_type = -1 expecting the data to be sorted descending, and then sorted it ascending (or vice versa), the binary search again goes in the wrong direction. The result is a wrong row with no error to investigate.

Wildcard lookups combined with approximate match

Wildcards (* and ?) only work with match_type = 0. If you accidentally use wildcards with match_type = 1, Excel ignores the wildcards and treats them as literal characters, often returning a wrong match without warning. Related wildcard pitfalls appear frequently in SUMIFS too — if you're seeing unexpected totals, the guide on SUMIFS returning incorrect totals with wildcard criteria walks through that class of error in detail.

Two-way lookup with incorrect match_type on either axis

Two-way INDEX MATCH uses two MATCH functions: one for the row, one for the column.

=INDEX(B2:D10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:D1, 0))

If either MATCH uses approximate match on unsorted data, you get a wrong row or a wrong column, and the combination multiplies the error. Always set both to 0 unless you specifically need range-based matching on that axis.

Verifying Your Fix

After you update the formula, don't just check one value. Run through these verification steps:

1. Test the first row, last row, and a middle row. Binary search failures tend to look correct for boundary values but fail mid-range.
2. Sort the data in a different order and re-run your lookups. If the results stay consistent, your formula is now order-independent.
3. Test a lookup value that doesn't exist in the range. With match_type = 0, a missing value returns #N/A, which is correct and honest. With approximate match, it would silently return a nearby row. If you need to handle missing values gracefully, wrap the formula: =IFERROR(INDEX(...), "Not found"). Just be careful not to hide genuine errors — the article on IFERROR masking real errors explains that trap in depth.
4. Check for duplicate values in the lookup range. MATCH with match_type = 0 returns the position of the first match. If your lookup column has duplicates and you need a different match, you'll need a more advanced approach like XLOOKUP with the search mode argument, or an array formula.

If you are also dealing with formula errors caused by cross-sheet references, the walkthrough on fixing INDIRECT #REF! errors when referencing another sheet covers how reference resolution can go wrong in ways that look similar to wrong-row returns.

Wrapping Up

The wrong-row bug in INDEX MATCH almost always comes down to one thing: relying on the default match_type of 1 on data that isn't guaranteed to stay sorted. Here's how to close that gap:

• Audit every MATCH formula in workbooks you own. If the third argument is missing or is 1 and the lookup range is user-editable data, add , 0.
• Make , 0 a habit. When you write a new INDEX MATCH, type all three arguments of MATCH as a matter of course, even before you test.
• Document intentional approximate-match formulas. Add a cell comment explaining that the range must stay sorted ascending. This prevents a future edit from silently breaking it.
• Verify formulas after any sort operation. If you sort a table that's used as a lookup source, spot-check the formulas that depend on it immediately after.
• Consider XLOOKUP for new workbooks. In Excel 365 and Excel 2021, XLOOKUP defaults to exact match and makes the intent explicit, removing the omitted-argument trap entirely.