Fixing Excel INDEX MATCH Returning Wrong Value on Unsorted Data

You've built your INDEX MATCH formula, it returns a value, and everything looks fine — until you spot that the result is completely wrong. No error, no warning, just quietly bad data flowing into your report. This is one of the most common silent bugs in Excel, and it comes down to a single argument you probably left blank.

• Why INDEX MATCH returns wrong values without throwing an error
• What the match_type argument actually controls
• How to fix your formula for unsorted data in one step
• How to verify your fix worked using a quick audit technique
• When approximate match is actually the right choice

Prerequisites

This guide applies to Excel 2016 and later, including Microsoft 365. The formulas shown work identically in Google Sheets. You should already know the basic structure of INDEX and MATCH — if not, a two-minute read on each function's syntax will make this article click faster.

How INDEX MATCH Works (The Short Version)

INDEX MATCH is two functions working together. MATCH finds the position of a value in a range and hands that position number to INDEX, which then retrieves the corresponding value from another range.

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

The match_type argument at the end of MATCH is what controls how the search is performed. It accepts three values: 1, 0, or -1. Most tutorials gloss over this, which is exactly where the trouble starts.

The Root Cause: Default Match Type Is Not Exact

When you omit match_type or pass 1, MATCH uses approximate match mode. In this mode, it assumes your lookup range is sorted in ascending order and uses a binary search to find the closest value that is less than or equal to your lookup value.

Binary search is fast, but it only works correctly on sorted data. On an unsorted list, it can jump to the wrong position, stop early, or land on a completely unrelated row. Excel doesn't warn you. It just returns whatever value happens to sit at the position the binary search lands on.

This is why your formula looks correct and passes a quick sanity check, yet produces wrong results for certain rows. The rows where it works are coincidences of data ordering, not correctness.

The One-Line Fix

Set match_type to 0. That's it.

=INDEX(B2:B100, MATCH(E2, A2:A100, 0))

With match_type set to 0, MATCH performs an exact match. It scans the lookup range sequentially and returns the position of the first cell that equals your lookup value exactly. Sort order is irrelevant. If the value exists, MATCH finds it. If it doesn't exist, you get a clean #N/A error instead of a wrong answer.

Compare the broken and fixed versions side by side:

How to Audit Your Existing Formulas

If you have a workbook you're unsure about, you can audit it quickly without reading every formula manually.

Use Find & Replace to spot missing match types

Press Ctrl+H to open Find & Replace, click Options, and check Look in: Formulas. Search for MATCH( and scan the results in the Find All list. Any formula that doesn't show , 0) or , -1) near the end of the MATCH call is a candidate for review.

Add a spot-check column

Next to your INDEX MATCH results, add a helper column that uses a strict exact match test:

=COUNTIF(A2:A100, E2)

If this returns 0, your lookup value doesn't exist in the lookup range at all, which means any non-error result from INDEX MATCH is definitely wrong. If it returns 1 or more and your result still looks suspicious, the match_type fix is your next step.

A Concrete Example

Suppose column A contains employee IDs in the order they were hired (not sorted numerically), and column B contains their department. You want to look up the department for employee ID 1042 stored in cell E2.

A         B
1005      Engineering
1042      Marketing
1017      Sales
1033      HR

With the broken formula =INDEX(B2:B5, MATCH(E2, A2:A5)), MATCH uses binary search on unsorted data. Depending on the values, it might return position 1 (Engineering) or position 3 (Sales) — neither is correct. With =INDEX(B2:B5, MATCH(E2, A2:A5, 0)), MATCH scans sequentially, finds 1042 at position 2, and INDEX returns