INDEX MATCH with Multiple Criteria

Copy-paste formula

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

Useful variations

=INDEX(D2:D100, MATCH(H2&"|"&H3, A2:A100&"|"&B2:B100, 0))

=INDEX(D2:D100, MATCH(H2&"|"&H3, E2:E100, 0))

=INDEX(D2:D100, MATCH(1, (A2:A100=H2)*(B2:B100=H3)*(C2:C100=H4), 0))

=INDEX(B2:E100, MATCH(H2, A2:A100, 0), MATCH(H3, B1:E1, 0))

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

=IFERROR(INDEX(D2:D100, MATCH(1, (A2:A100=H2)*(B2:B100=H3), 0)), "Not found")

=INDEX(D2:D100, MATCH(1, (A2:A100="A-100")*(B2:B100="East"), 0))

=FILTER(A2:D100, A2:A100=H2, B2:B100=H3)

=XLOOKUP(1, (A2:A100=H2)*(B2:B100=H3), D2:D100, "Not found")

Sample data

When to use this formula

• Use this when the lookup key is split across two or more columns, such as SKU plus Region.
• Use INDEX MATCH instead of VLOOKUP, because VLOOKUP can only match a single column.
• Use the array method for reliability, or the concatenation method when you want a shorter formula.
• Use XLOOKUP instead on Excel 365 or Google Sheets if you want not-found handling built in.
• Use SUMIFS instead when you need to total values by several criteria rather than return one value.

Two criteria lookup in Excel

For a two-criteria lookup, keep the return range in INDEX and put both tests inside MATCH.

The lookup value for MATCH is 1 because multiplying TRUE/FALSE arrays creates 1 only on rows where both conditions match.

Use cell references such as H2 and H3 when users choose the lookup values, or quoted text such as "A-100" when the criteria are fixed.

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

=INDEX(D2:D100, MATCH(1, (A2:A100="A-100")*(B2:B100="East"), 0))

Business case: SKU and region price

Use this pattern when one SKU can have different values by region, month, channel, or customer type.

In the sample data, H2 contains A-100 and H3 contains East. The formula finds the row where both values match and returns the aligned Price from column D.

How the multiple-criteria array works

(A2:A100=H2) returns an array of TRUE and FALSE for the first criterion, and (B2:B100=H3) does the same for the second.

Multiplying the two arrays converts them to 1 and 0, giving 1 only on rows where both criteria are true.

MATCH(1, ..., 0) finds the first row that equals 1, and INDEX returns the aligned value from the return column.

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

Array vs concatenation method

The array method multiplies conditions and works with numbers, text, and dates without extra setup.

The concatenation method joins the key columns with & and is shorter to read, but a separator is required to avoid false matches.

=INDEX(D2:D100, MATCH(H2&"|"&H3, A2:A100&"|"&B2:B100, 0))

Helper column method for older workbooks

A helper column makes the lookup key visible in the sheet, which is useful when older Excel users need to debug the match row.

Create a helper key beside the source data, then match the lookup key against that single helper range.

=A2&"|"&B2

=INDEX(D2:D100, MATCH(H2&"|"&H3, E2:E100, 0))

Row and column two-way lookup

Use a two-way lookup when one input selects the row, such as SKU, and another input selects the return column, such as Price, Stock, or Cost.

The first MATCH finds the row position, and the second MATCH finds the column position inside the return table.

=INDEX(B2:E100, MATCH(H2, A2:A100, 0), MATCH(H3, B1:E1, 0))

=INDEX(D2:F100, MATCH(1, (A2:A100=H2)*(B2:B100=H3), 0), MATCH(H4, D1:F1, 0))

Excel 365, Excel 2021, older Excel, and Google Sheets

Excel 365 and Excel 2021 accept the array formula with a normal Enter.

Excel 2019 and earlier need Ctrl+Shift+Enter, which shows the formula wrapped in curly braces.

Google Sheets accepts the same array formula with a normal Enter. Use FILTER instead when you want every matching row rather than the first matching value.

Troubleshoot not found errors in INDEX MATCH multiple criteria

#N/A means MATCH did not find a row where every criteria test became TRUE.

Check that the return range and every criteria range start and end on the same rows, then confirm the criteria values do not contain hidden spaces or mismatched number/date formats.

In Excel 2019 and earlier, also confirm the formula with Ctrl+Shift+Enter. If the formula is entered normally in older Excel, MATCH may not evaluate the array.

=IFERROR(INDEX(D2:D100, MATCH(1, (A2:A100=H2)*(B2:B100=H3), 0)), "Not found")

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

Duplicate matches and first-match behavior

INDEX MATCH returns the first row where all criteria match.

If your table can contain duplicate SKU and Region combinations, add another condition such as Month, Customer, or Status so the lookup key becomes unique.

Use FILTER in Google Sheets or Excel 365 when the goal is to inspect every matching row instead of returning one value.

=INDEX(D2:D100, MATCH(1, (A2:A100=H2)*(B2:B100=H3)*(C2:C100=H4), 0))

=FILTER(A2:D100, A2:A100=H2, B2:B100=H3)

INDEX MATCH multiple criteria in Google Sheets

The same array formula works in Google Sheets with a normal Enter; no ArrayFormula wrapper is needed inside MATCH.

To return every matching row instead of the first, use FILTER.

=FILTER(D2:D100, A2:A100=H2, B2:B100=H3)

Replace these ranges before copying

INDEX MATCH multiple criteria error checklist

INDEX MATCH vs XLOOKUP vs FILTER vs SUMIFS

Returned result from the sample data

Input method by spreadsheet version

Formula explanation

• (A2:A100=H2) returns TRUE or FALSE for the first criterion on each row.
• Multiplying by (B2:B100=H3) gives 1 only where both criteria are true.
• MATCH(1, ..., 0) finds the position of the first row that equals 1.
• INDEX(D2:D100, position) returns the value from the return column on that row.

Common errors

• Range heights differ: the criteria ranges and the return range must all cover the same rows.
• No matching combination returns #N/A unless you wrap the formula in IFERROR.
• Duplicate key combinations return the first matching row only.
• In Excel 2019 and earlier, MATCH returns #N/A unless you press Ctrl+Shift+Enter.
• Plain concatenation can give false matches unless you add a separator such as H2&"|"&H3.

Build your own version

Start with the INDEX MATCH builder for a single-criterion lookup, then add an extra (range=value) factor for each additional column. INDEX MATCH Formula Builder.

Related formulas

• XLOOKUP with Multiple Criteria
• SUMIFS with Multiple Criteria
• Lookup with Multiple Criteria
• INDEX MATCH Formula Builder
• VLOOKUP vs XLOOKUP vs INDEX MATCH
• INDEX MATCH Formula
• INDEX MATCH Left Lookup