INDEXMATCHMultiple CriteriaLookupXLOOKUP

INDEX MATCH with Multiple Criteria

INDEX MATCH with multiple criteria lets you match on two or more columns in Excel and Google Sheets. Learn the exact array formula plus a cleaner XLOOKUP alternative.

Introduction

INDEX and MATCH are a classic pair for flexible lookups, but a single MATCH can only match one column. When your lookup depends on two or more keys at once — say product and region — you need to combine conditions into the formula. This guide shows the array-based INDEX MATCH technique and a modern XLOOKUP shortcut.

Prerequisites

Basic INDEX and MATCH syntax
Understanding of TRUE/FALSE Boolean arithmetic
Microsoft 365 (only needed for the XLOOKUP method)

1The Basic INDEX MATCH Pattern

On its own, INDEX MATCH looks up one value and returns another from any column. MATCH finds the row position where a single condition is true; INDEX then returns the value at that row in a different column. This already works to the left, unlike VLOOKUP.

Example

=INDEX(C2:C100, MATCH("Apple", A2:A100, 0))

Result: Returns the value in column C where column A equals "Apple"

MATCH("Apple", A2:A100, 0) returns the row number; INDEX uses it to pull the matching value from column C.

Use 0 as the match_type for an exact match — the most common case.

2Adding a Second Criterion

To match on two columns, multiply two TRUE/FALSE conditions inside MATCH. Each condition returns an array of TRUE/FALSE, which Excel turns into 1/0 when multiplied. Only the row where BOTH are true produces 1, so MATCH(1, ...) finds that exact row.

Build the first condition

Test the first key against its column.

=(A2:A100=G2)

Build the second condition

Test the second key against its column.

=(B2:B100=H2)

Multiply and match

Multiply the conditions and look for the first 1.

=MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0)

Example

=INDEX(D2:D100, MATCH(1, (A2:A100=G2)*(B2:B100=H2), 0))

Result: Returns the column D value where column A = G2 AND column B = H2

In legacy Excel this is an array formula — confirm with Ctrl+Shift+Enter. In Microsoft 365 it spills automatically. Google Sheets also evaluates it natively without special entry.

Keep lookup ranges the same size as the return range (here both span rows 2 to 100).

Add a delimiter helper like (A2:A100=G2)*(B2:B100="North") for text keys.

3Three or More Criteria

You are not limited to two keys. Just multiply additional conditions into the same MATCH. Each new factor is AND-ed, so every condition must be true for the row to match.

Example

=INDEX(E2:E100, MATCH(1, (A2:A100=G2)*(B2:B100=H2)*(C2:C100=I2), 0))

Result: Returns column E where A = G2, B = H2, AND C = I2

The product of three TRUE/FALSE arrays is 1 only on the row satisfying all three conditions.

Wrap the whole formula in IFERROR(..., "Not found") to handle missing combinations gracefully.

4XLOOKUP for a Cleaner Alternative

If you are on Microsoft 365 or Google Sheets, XLOOKUP accepts array conditions directly, so multiple criteria are simply AND-ed inside the lookup_value. No array-entry, no row math, and it can return whole columns.

Example

=XLOOKUP(1, (A2:A100=G2)*(B2:B100=H2), D2:D100)

Result: Returns the column D value matching both criteria

XLOOKUP finds the first 1 in the combined condition array and returns the corresponding value from D2:D100. Add a 4th argument like "Not found" for missing matches.

XLOOKUP also looks in any direction and avoids the column-index fragility of VLOOKUP.

5When to Use SUMIFS Instead

If your goal is not a single matching cell but an aggregated total for the matching rows, SUMIFS is simpler and needs no array entry. It sums a range where every criterion is true, which covers the common 'total sales for product X in region Y' case.

Example

=SUMIFS(D2:D100, A2:A100, G2, B2:B100, H2)

Result: Sum of column D where A = G2 AND B = H2

Each range/criteria pair is combined with AND logic, so only rows meeting all conditions are summed.

Use SUMIFS when the answer is a number total; use INDEX MATCH or XLOOKUP when you need a specific cell value.

Functions Used

INDEX

INDEX returns the value at a specified position in a range or array.

MATCH

MATCH searches for a specified item in a range and returns its relative position.

VLOOKUP

VLOOKUP (Vertical Lookup) is a built-in function in Excel and Google Sheets that searches for a value in the first column of a table and returns a value in the same row from a specified column.

SUMIFS

SUMIFS adds values that satisfy multiple criteria across one or more ranges.

Related Guides

VLOOKUP with Multiple Criteria

VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.

INDEX MATCH Formula

Master the INDEX MATCH formula in Excel and Google Sheets step by step: exact syntax, left lookups, two-way lookups, and how it compares to VLOOKUP and XLOOKUP.

Master INDEX/MATCH for Two-Way Lookups

Learn how to use INDEX and MATCH together to create powerful two-way lookups that work in any direction, unlike VLOOKUP.

Summary

INDEX MATCH with multiple criteria multiplies several TRUE/FALSE conditions inside MATCH(1, ...) to locate the single row where every key matches. In Microsoft 365 or Google Sheets, XLOOKUP does the same with cleaner syntax, while SUMIFS is the better tool when you need an aggregated total rather than one cell. Pick INDEX MATCH or XLOOKUP for value lookups and SUMIFS for sums.

Next Steps

Try the two-criteria INDEX MATCH on a dataset where the result is left of the keys
Switch to XLOOKUP if you are on Microsoft 365 for a no-array-entry version
Use SUMIFS when the answer should be a summed total across matching rows