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

You switched from VLOOKUP to INDEX MATCH specifically to avoid sorting requirements, but the formula is still coming back with the wrong value. The frustrating part is that it looks correct — the syntax checks out, there are no #N/A errors, just silently wrong data.

The root cause is almost always a single optional argument you either left out or set incorrectly. Here is exactly how to find it and fix it.

What You'll Learn

• Why INDEX MATCH quietly returns the wrong row on unsorted data
• How the match_type argument controls the behavior
• How to confirm which row MATCH is actually landing on
• How to handle duplicates, last-match scenarios, and multi-criteria lookups
• The most common pitfalls that look like sorting issues but aren't

What Actually Causes INDEX MATCH to Return the Wrong Row

The INDEX MATCH combination works by having MATCH find a position (a row number) and INDEX pull the value at that position. If MATCH hands back the wrong row number, INDEX faithfully returns whatever is in that row — no error, no warning.

The formula structure you're probably using looks like this:

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

That third argument to MATCH — match_type — is optional. When you leave it out, Excel defaults it to 1, not 0. And that default is the reason unsorted data produces wrong results.

The match_type Argument: The Most Overlooked Parameter

MATCH supports three modes, and each one assumes something different about your data:

match_type	Behavior	Data must be sorted?
1 (or omitted)	Finds the largest value less than or equal to the lookup value	Yes — ascending order required
0	Finds the first exact match	No
-1	Finds the smallest value greater than or equal to the lookup value	Yes — descending order required

When match_type is 1 and your data is not sorted in ascending order, Excel uses a binary search. It makes assumptions about where to look based on sort order, jumps into the middle of the range, and can land in entirely the wrong section. It will return a position — just the wrong one — with no error to tip you off.

This is also exactly the same mechanism that trips up VLOOKUP when you accidentally leave its range_lookup argument as TRUE. If you have dealt with that before, you have already seen this bug's cousin. If you want a broader look at how a similar silent failure plays out in Excel's criteria-based functions, see why SUMIF returns zero even when the criteria look correct — the underlying data-type mismatch problem often overlaps.

How to Verify Which Row Your MATCH Is Landing On

Before you fix anything, confirm the diagnosis. Pull the MATCH part out of the formula and evaluate it on its own. Click an empty cell and type:

=MATCH(lookup_value, lookup_range, 1)

Note the row number it returns. Then look at your actual data in that row. If it is not the row you expected, the match_type is the problem.

Now run the same test with match_type set to 0:

=MATCH(lookup_value, lookup_range, 0)

If the row number changes and the new result matches your expected row, you have confirmed the issue. The fix is straightforward from here.

Fixing the Formula: Force an Exact Match

Change your match_type from its default (or from 1) to 0. That single character is the fix for the majority of wrong-row problems on unsorted data.

Before:

=INDEX(C2:C100, MATCH(F2, A2:A100))

After:

=INDEX(C2:C100, MATCH(F2, A2:A100, 0))

With match_type set to 0, MATCH scans the entire lookup range linearly from top to bottom until it finds an exact match. It does not care about sort order at all. If the value is not found, you get an honest #N/A error rather than a wrong answer — which is far better behavior for debugging.

If you want to suppress the #N/A in cases where the lookup value might legitimately be missing, wrap the whole thing in IFERROR:

=IFERROR(INDEX(C2:C100, MATCH(F2, A2:A100, 0)), "Not found")

Use this carefully — hiding errors is fine for a finished report, but it can mask real data problems during development.

When You Need the Last Match Instead of the First

With match_type set to 0, MATCH returns the position of the first occurrence of your lookup value. If you have duplicate entries and need the last one — for example, the most recent transaction for a customer — you need a different approach.

The classic technique uses MATCH in a way that scans from the bottom up by inverting a conditional array:

=INDEX(C2:C100, MATCH(1, 0+(A2:A100=F2), 0) + ... )

A cleaner pattern that works in most Excel versions is to use MATCH with a lookup array constructed so the last match becomes the first hit:

=INDEX(C2:C100, MATCH(2, 1/(A2:A100=F2), 1))

Here is how this works: 1/(A2:A100=F2) produces an array of 1s (where the condition is true) and #DIV/0! errors (where it is false). MATCH with match_type of 1 searches for the largest value less than or equal to 2, which is 1, and because it scans ascending, it naturally lands on the last 1 in the array. On modern Excel (Microsoft 365 or Excel 2019+), you can confirm this as a regular formula. On older versions, enter it with Ctrl+Shift+Enter to make it an array formula.

Handling Duplicate Values in the Lookup Column

Duplicates introduce ambiguity that no single formula can resolve without additional context. The question you need to answer first is: which duplicate do you actually want? Options include the first, the last, one matching an additional condition, or an aggregated result across all of them.

For the first match, the exact-match fix above (match_type = 0) already handles this correctly.

For a match based on a secondary condition, move to a multi-criteria lookup covered in the next section.

For aggregating across all matches (sum, count, average), INDEX MATCH is the wrong tool — reach for SUMIF, COUNTIF, or AVERAGEIF instead. A related scenario where criteria-based aggregation fails silently is worth reviewing: debugging SUMIF when it returns zero despite correct-looking criteria.

Using Multiple Criteria to Narrow Down the Right Row

If your lookup column has duplicates and you need a specific one, the solution is to match on two or more columns simultaneously. The standard pattern uses an array formula that multiplies Boolean conditions together:

=INDEX(D2:D100, MATCH(1, (A2:A100=F2)*(B2:B100=G2), 0))

Breaking this down:

• (A2:A100=F2) produces a TRUE/FALSE array for column A matches.
• (B2:B100=G2) produces a TRUE/FALSE array for column B matches.
• Multiplying them together gives a 1 only where both conditions are true, 0 everywhere else.
• MATCH then finds the first row where the result is 1.

In Excel 365, this works as a regular formula. In Excel 2016 and earlier, press Ctrl+Shift+Enter to enter it as an array formula — you will see curly braces { } appear around it in the formula bar.

If you are on Microsoft 365, also consider XLOOKUP as a simpler alternative for multi-criteria scenarios. It supports exact matching by default and has cleaner syntax for returning values when a match is not found.

Common Pitfalls That Look Like Sorting Bugs

Once you have fixed match_type, you might still get wrong or unexpected results. Here are the other culprits worth checking:

Data type mismatches

If your lookup value is the number 1001 but the lookup range contains the text "1001", MATCH with match_type = 0 will return #N/A even though the values look identical on screen. Use =ISNUMBER(A2) on a few cells to check whether your IDs are stored as numbers or text. Then either convert the range with VALUE() or wrap your lookup value: MATCH(TEXT(F2,