How to Fix Excel INDEX MATCH Returning Wrong or #N/A Results

In this tutorial, you'll learn about How to Fix Excel INDEX MATCH Returning Wrong or #N/A Results. We cover key concepts, practical examples, and best practices.

INDEX MATCH is a flexible lookup combination that searches any column independently. When it returns the wrong value or #N/A, the row and column ranges are misaligned, the MATCH is looking at the wrong data, or the match type is incorrect.

The Problem

=INDEX(C:C, MATCH(D2, A:A, 0))

Returns the wrong value from column C, or #N/A when the value should exist.

Wrong approach — adding more nested IFs instead of fixing the range alignment.

The Fix

Ensure the INDEX column and MATCH column have the same starting row:

Wrong: =INDEX(C1:C100, MATCH(D2, A:A, 0))       ← row 1 vs whole column mismatch
Right: =INDEX(C:C, MATCH(D2, A:A, 0))            ← both use whole column

For two-way lookups (row and column), combine two MATCH functions:

= INDEX(B2:E10, MATCH(H2, A2:A10, 0), MATCH(I2, B1:E1, 0))

This finds the row by a value in column A and the column by a header in row 1.

For approximate match (sorted data):

= INDEX(C:C, MATCH(D2, A:A, 1))    ← match type 1, requires ascending sort

For multiple criteria, use array syntax:

= INDEX(C:C, MATCH(1, (A:A=D2)*(B:B=E2), 0))

Enter with Ctrl+Shift+Enter in older Excel, or normally in Excel 365.

Expected output:

INDEX MATCH returns the correct value from the result column
#N/A only when the value is truly missing
Two-way lookup returns the intersection of row and column

Prevention Tips

• Keep INDEX and MATCH ranges the same size to avoid alignment errors
• Use 0 (exact match) as the MATCH type unless you specifically need approximate matching
• Test the MATCH function separately in a cell to see what row it returns
• Use named ranges to make formulas readable: =INDEX(Prices, MATCH(Product, ProductList, 0))
• Consider XLOOKUP as a simpler alternative in Excel 365 for single-criteria lookups

Common Mistakes with index match

1. Forgetting that lazy evaluation defers computation until the value is forced, causing space leaks with unevaluated thunks
2. Using return to exit a function early instead of wrapping a pure value in the monad
3. Mixing let bindings with <- bindings in do notation, producing type errors

These mistakes appear frequently in real-world EXCEL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Related: DodaTech's Formula Reference Checker validates INDEX MATCH range alignment, detects off-by-one errors, and suggests XLOOKUP alternatives for newer Excel versions. Use with DodaZIP for batch formula auditing.