Google Sheets INDEX MATCH Multiple Criteria Not Working? Here’s What To Do

Your INDEX MATCH formula isn’t working when you try to match multiple conditions, it returns #N/A, wrong values, or fails completely.
This happens because standard MATCH only handles one lookup condition unless structured correctly.

This guide shows how to fix it and build reliable multi-criteria lookups.

Why the Issue Happens

Using standard MATCH (single condition only)
Not converting conditions into a combined array
Missing array formula logic
Data type mismatch (text vs number)
Extra spaces or hidden characters
Incorrect range alignment
Duplicate matches causing confusion

Step-by-Step Fixes

Step 1: Use Correct Multi-Criteria Structure

Standard formula won’t work:

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

For multiple criteria, use:

=INDEX(C2:C100, MATCH(1, (A2:A100=E1)*(B2:B100=F1), 0))

(A2:A100=E1) → condition 1
(B2:B100=F1) → condition 2
* → acts as AND
1 → match where both are TRUE

Step 2: Ensure Correct Range Alignment

All ranges must be same size.

Wrong:

=INDEX(C2:C50, MATCH(1, (A2:A100=E1)*(B2:B100=F1), 0))

Correct:

=INDEX(C2:C100, MATCH(1, (A2:A100=E1)*(B2:B100=F1), 0))

Step 3: Fix Data Type Mismatch

Example:

100 vs "100"

Fix:

=INDEX(C2:C100, MATCH(1, (VALUE(A2:A100)=E1)*(B2:B100=F1), 0))

Step 4: Remove Extra Spaces

Hidden spaces break matching.

Fix:

=INDEX(C2:C100, MATCH(1, (TRIM(A2:A100)=E1)*(TRIM(B2:B100)=F1), 0))

Step 5: Handle Case Sensitivity (If Needed)

Default MATCH is not case-sensitive.

For strict matching:

=INDEX(C2:C100, MATCH(1, (EXACT(A2:A100,E1))*(EXACT(B2:B100,F1)), 0))

Step 6: Debug Each Condition Separately

Test:

=A2:A100=E1

=B2:B100=F1

If either returns FALSE where it shouldn’t, fix data.

Step 7: Handle No Match (#N/A)

Wrap with IFERROR:

=IFERROR(INDEX(C2:C100, MATCH(1, (A2:A100=E1)*(B2:B100=F1), 0)), "Not Found")

Step 8: Return Multiple Matches (If Needed)

INDEX MATCH returns only first match.

Use FILTER:

=FILTER(C2:C100, (A2:A100=E1)*(B2:B100=F1))

Returns all matching results.

Step 9: Use Helper Column (Simpler Approach)

Create combined key:

=A2 & "|" & B2

Then:

=VLOOKUP(E1&"|"&F1, D2:E100, 2, FALSE)

Simplifies logic.

Step 10: Use XLOOKUP (Cleaner Alternative)

=XLOOKUP(1, (A2:A100=E1)*(B2:B100=F1), C2:C100)

Common Mistakes

Using standard MATCH for multiple conditions
Not multiplying conditions (*)
Misaligned ranges
Ignoring text vs number mismatch
Not cleaning spaces
Expecting multiple results from INDEX MATCH
Not debugging conditions individually

Pro Tips / Better Alternatives

Use FILTER for Simpler Logic

=FILTER(C2:C100, A2:A100=E1, B2:B100=F1)

Cleaner and easier to debug.

Use QUERY for Structured Matching

=QUERY(A1:C100, "SELECT C WHERE A='value1' AND B='value2'", 1)

Works well for large datasets.

Combine Conditions with ARRAYFORMULA

=ARRAYFORMULA(INDEX(C2:C100, MATCH(1, (A2:A100=E1)*(B2:B100=F1), 0)))

Useful for scalable models.

Clean Data Before Matching

Use:

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

Avoid Full Column References

Instead of:

A:A