INDEX MATCH With Multiple Criteria in Excel

INDEX MATCH looks up a value based on one condition by default, but you can extend it to match on two or more columns at once by multiplying boolean comparisons together inside MATCH — no helper column required.

How does the boolean multiplication trick work?

Each comparison like $A$2:$A$100=F2 produces an array of TRUE/FALSE values, one per row; multiplying two such arrays together turns TRUE*TRUE into 1 and anything else into 0, so only the row where both conditions are true produces a 1 for MATCH to find.

=INDEX($D$2:$D$100,
    MATCH(1,
        ($A$2:$A$100=F2) * ($B$2:$B$100=G2),
    0)
)

F2 = "West", G2 = "Q3"
Row where column A = "West" AND column B = "Q3" → the only 1
MATCH(1, ...) finds that row's position
INDEX returns column D's value at that position

Add a third condition by multiplying in another comparison: ($A2:$A100=F2)*($B2:$B100=G2)*($C2:$C100=H2) — the pattern extends to any number of criteria the same way.

Do I need Ctrl+Shift+Enter for this formula?

It depends on your Excel version: 365 and 2021 evaluate array math natively so a plain Enter works, but Excel 2019 and earlier require confirming the formula as an array formula or it returns the wrong result or an error.

Excel 365 / 2021: type the formula, press Enter — done.
Dynamic arrays handle the multiplication automatically.

Excel 2019 and earlier: type the formula, then press
Ctrl+Shift+Enter instead of Enter. Excel wraps the formula
in curly braces { } to show it's an array formula. Typing
the braces yourself does not work — they only appear when
you confirm with Ctrl+Shift+Enter.

See entering an array formula in Excel for the full Ctrl+Shift+Enter walkthrough if you're on a pre-365 version.

What happens if no row matches both criteria, or more than one does?

No match returns #N/A from MATCH, which you should wrap in IFERROR; multiple matches silently return only the first one MATCH finds, which is rarely what you want for one-to-many data.

Handle no match:
=IFERROR(
    INDEX($D$2:$D$100, MATCH(1, ($A$2:$A$100=F2)*($B$2:$B$100=G2), 0)),
    "Not found"
)

If your criteria combination can legitimately match more than one row (e.g. multiple transactions for the same customer and date), INDEX MATCH will only ever return the first one — use SUMIFS or a pivot table instead when you actually need to aggregate, not just look up.

How does this compare to XLOOKUP for multiple criteria?

XLOOKUP uses the same boolean multiplication idea but with cleaner syntax and built-in error handling, and it's available if your organization is on Excel 365 or 2021.

XLOOKUP with multiple criteria:
=XLOOKUP(1, ($A$2:$A$100=F2)*($B$2:$B$100=G2), $D$2:$D$100, "Not found")

Same logic as INDEX/MATCH, but:
- No array-entry concerns (dynamic arrays handle it)
- Built-in "if not found" argument replaces IFERROR
- One function instead of two nested ones

Factor	INDEX MATCH	XLOOKUP
Excel version required	2007 onward	365 or 2021+
Array-entry needed pre-365?	Yes (Ctrl+Shift+Enter)	N/A — not available pre-365
Not-found handling	Wrap in IFERROR separately	Built-in 4th argument
Functions involved	Two (INDEX + MATCH)	One

Both approaches use the exact same multiplication logic underneath — XLOOKUP just packages it more cleanly. See INDEX MATCH explained for the single-criteria basics, XLOOKUP with multiple criteria for the full XLOOKUP version, and VLOOKUP with multiple criteria if you're stuck matching this pattern to a VLOOKUP-based sheet.

What are common mistakes with multi-criteria INDEX MATCH?

Most failures come from mismatched range sizes or forgetting the array-entry step on older Excel versions.

• Mismatched range sizes: $A$2:$A$100 and $B$2:$B$100 must cover the exact same number of rows as $D$2:$D$100, or the position MATCH returns won't line up with INDEX's range
• Forgetting Ctrl+Shift+Enter pre-365: without it, the formula either errors out or silently returns the wrong row
• Using text criteria with mismatched case or spacing: comparisons are not case-sensitive but trailing spaces do break an exact match
• Expecting multiple results: INDEX MATCH always returns one value — if several rows match, only the first one wins