Google Sheets INDEX MATCH Not Working? Here’s How To Fix It

Your INDEX MATCH formula in Google Sheets is returning #N/A, #REF!, or incorrect values.
This usually happens due to range misalignment, wrong match type, or data inconsistencies.

This guide shows exactly how to fix it.

Why the Issue Happens

• MATCH not set to exact match (0)
• Lookup and return ranges are different sizes
• Text vs number mismatch
• Extra spaces or hidden characters
• Lookup value not present
• Incorrect nesting of INDEX and MATCH
• Duplicate values causing unexpected results
• Using approximate match on unsorted data

Step-by-Step Fixes

Step 1: Use Correct INDEX MATCH Structure

Correct formula:

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

Example:

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

• A2 → lookup value
• A2:A100 → lookup range
• B2:B100 → return range
• 0 → exact match

If structure is wrong, results will fail.

Step 2: Force Exact Match in MATCH

If you don’t use 0, results can be incorrect.

Fix:

=MATCH(A2, A2:A100, 0)

Never leave it blank or use 1 unless data is sorted.

Step 3: Fix #N/A (Value Not Found)

If you see #N/A, MATCH is failing.

Check:

• Does the value exist in the lookup range?
• Is formatting consistent?

Handle safely:

=IFERROR(INDEX(B2:B100, MATCH(A2, A2:A100, 0)), "Not Found")

Step 4: Fix Range Alignment

Ranges must be the same size.

Wrong:

=INDEX(B2:B50, MATCH(A2, A2:A100, 0))

Correct:

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

Mismatch leads to incorrect or failed results.

Step 5: Fix Text vs Number Mismatch

Example:

• Lookup value = 101 (number)
• Table value = "101" (text)

Fix:

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

or:

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

Step 6: Remove Extra Spaces

Hidden spaces prevent matching.

Fix:

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

To clean data:

=ARRAYFORMULA(TRIM(A2:A100))

Step 7: Handle Duplicate Values

INDEX MATCH returns the first match only.

If duplicates exist, results may not be what you expect.

Alternative:

=FILTER(B2:B100, A2:A100=A2)

Returns all matches.

Step 8: Fix #REF! Errors

Occurs when INDEX points outside range.

Cause:

• MATCH returns position beyond INDEX range

Fix:

• Ensure both ranges match exactly
• Check for incorrect row indexing

Step 9: Avoid Full Column References

Using:

=INDEX(B:B, MATCH(A2, A:A, 0))

can slow performance in large sheets.

Better:

=INDEX(B2:B1000, MATCH(A2, A2:A1000, 0))

Step 10: Debug MATCH Separately

If formula fails, test MATCH alone:

=MATCH(A2, A2:A100, 0)

If this returns #N/A, the issue is in the lookup—not INDEX.

Common Mistakes

• Forgetting 0 in MATCH
• Misaligned ranges
• Ignoring text vs number mismatch
• Not cleaning spaces in data
• Expecting multiple results from INDEX MATCH
• Using approximate match unintentionally
• Not testing MATCH separately

Pro Tips / Better Alternatives

Use XLOOKUP (Cleaner and More Flexible)

=XLOOKUP(A2, A2:A100, B2:B100)

• No need for MATCH
• Works left and right
• Simpler syntax

Use FILTER for Multiple Matches

=FILTER(B2:B100, A2:A100=A2)

Returns all matching results.

Use ARRAYFORMULA for Bulk Lookup

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

Applies lookup across multiple rows.

Use EXACT for Debugging

=EXACT(A2, A3)

Checks if values truly match.

Clean Data Before Lookup

Use:

=TRIM()
=CLEAN()
=VALUE()

Clean data prevents lookup failures.

Bottom Line

If INDEX MATCH isn’t working, fix in this order:

1. Use correct formula structure
2. Set MATCH to exact (0)
3. Align lookup and return ranges
4. Fix data types (text vs number)
5. Remove extra spaces
6. Test MATCH separately

Most issues come from data mismatch and range errors.
Fix those, and INDEX MATCH will work reliably.