INDEX MATCH With Multiple Criteria: Excel Examples

In Excel for Microsoft 365, use INDEX with MATCH and multiply the criteria tests to find a row that meets several conditions. For example, with departments in A2:A100, products in B2:B100, sales in D2:D100, and lookup values in G2 and H2, use =INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=G2)*($B$2:$B$100=H2),0)); it returns the sales value from the first row matching both criteria.

Set up the data and lookup values

The formula works by testing corresponding cells in each criteria range. To make the examples concrete, imagine a sales table with Department in column A, Product in column B, Region in column C, and Units in column D. Each row represents one record. Put the department to find in G2 and the product to find in H2. The first formula returns Units for the first row where both fields match.

For instance, if row 2 is Books, Atlas, East, 12 and row 3 is Books, Atlas, West, 18, looking for Books and Atlas returns 12. Both records meet those two criteria, so the formula returns the first one in worksheet order. If you need to distinguish the two records, include Region as another criterion.

Keep the ranges the same size and aligned: A2:A100, B2:B100, and D2:D100 all begin and end on corresponding rows. If one criteria range starts at row 2 but another starts at row 3, Excel compares the wrong records or may return an error. Avoid including header cells in one range but not the others.

How the multiple-criteria formula works

INDEX returns a value from a result range at a specified position. MATCH finds a position in a lookup array. In this expression, each comparison creates a series of TRUE or FALSE results: A2:A100=G2 checks the department, and B2:B100=H2 checks the product. Multiplying the tests converts TRUE to 1 and FALSE to 0. A row gets 1 only when every test is true.

MATCH(1, …, 0) searches for the first 1, with the final 0 specifying an exact match. INDEX then returns the item at that position from the sales range. The two tests therefore act like an AND condition: both must be satisfied. Add another condition by multiplying another comparison into the MATCH lookup array.

In Microsoft 365 for Windows, enter this formula normally with Enter. You do not need Ctrl+Shift+Enter for this array calculation. The formula uses ordinary worksheet functions and works with the dynamic-array calculation engine. Use dollar signs on the data ranges if you plan to fill the formula down, while leaving G2 and H2 relative so the lookup cells can change by row.

Example with three criteria

Suppose you want to find Units by Department, Product, and Region. With departments in A2:A100, products in B2:B100, regions in C2:C100, units in D2:D100, and the requested department, product, and region in G2, H2, and I2, use:

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

If G2 is Books, H2 is Atlas, and I2 is East, and the first row with all three values has 12 in column D, the result is 12. A Books/Atlas/West row is excluded because its region does not match. To add a fourth criterion, multiply by another comparison, such as a date range compared with a date in J2. All tests must correspond to the same record for that row to qualify.

This formula returns a value, not the matching row number. If the result values are text, INDEX returns the matching text; if they are dates, it returns the underlying date value, which Excel displays according to the result cell’s number format. If multiple records satisfy every condition, only the first match is returned.

Exact matches, duplicates, and criteria meaning

The criteria comparisons use equality, so they look for the same cell value rather than an approximate match. Text comparisons in Excel are generally not case-sensitive: Atlas and ATLAS are treated as equal here. Leading or trailing spaces, however, can make values differ. A cell containing “Atlas ” with an extra space may not match a lookup value containing “Atlas” without it.

Duplicate matches are not automatically an error. MATCH finds the first qualifying row from the top of the searched ranges. If records are sorted differently, the first returned value may change. Decide whether first-match behavior is appropriate before relying on the result for reporting. If the intended key should identify exactly one record, check the source data for duplicates rather than assuming the formula does so.

Blank lookup cells also deserve attention. A comparison to a blank criterion can match blank cells in that field. If blanks should not be accepted, require a nonblank lookup value or add a condition that checks the relevant field is not empty. Also note that a formula returning an empty string is not always identical to a truly empty cell.

Return every matching result with FILTER

INDEX MATCH is a good fit when you need one result. If you want every matching value, Microsoft 365’s FILTER function can return a spill range. With departments in A2:A100, products in B2:B100, units in D2:D100, and criteria in G2 and H2, use:

=FILTER($D$2:$D$100,($A$2:$A$100=G2)*($B$2:$B$100=H2),"No match")

For the Books and Atlas example, this returns the Units values for all qualifying rows, including both 12 and 20 if those are the two matching records. The results spill into cells below the formula, so make sure that area is clear. FILTER is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not have it. Unlike INDEX MATCH, FILTER preserves all matching rows rather than selecting the first.

Common errors and practical checks

#N/A usually means no row produced a 1 for all the criteria, although mismatched range sizes or inconsistent data can also cause problems. Check spelling, spaces, number-versus-text differences, and whether the criteria values are in the intended cells. A number stored as text may look identical to a numeric value but fail an equality comparison.

To display a clear message when no match exists, wrap the lookup in IFNA. With the same ranges and lookup cells as the two-criteria example, use:

=IFNA(INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=G2)*($B$2:$B$100=H2),0)),"No matching row")

This returns the matching value when found and the text No matching row when MATCH returns #N/A. IFNA only handles the #N/A error; it does not conceal every possible formula error, which is useful when you want other problems to remain visible. During troubleshooting, test each comparison separately or inspect a few source rows to verify that the criteria actually line up.

For performance, avoid unnecessarily large full-column array references in a workbook with many formulas. A bounded range such as rows 2 through 10,000, or a properly structured Excel Table, is easier to manage. Keep the source and result ranges consistent if you extend the data. Sorting is not required for this exact-match formula.

Alternative methods and when to choose them

A helper column can make the logic easier to audit. With Department in A2 and Product in B2, place this in E2 and fill it down:

=A2&"|"&B2

With the combined keys in E2:E100, the result values in D2:D100, and lookup criteria in G2 and H2, use:

=INDEX($D$2:$D$100,MATCH(G2&"|"&H2,$E$2:$E$100,0))

The delimiter separates the fields so that, for example, AB and C do not accidentally create the same key as A and BC. Still, if the delimiter itself can occur in the source values, combined keys can collide. The direct multiple-condition formula avoids that particular delimiter issue and does not require an extra column.

XLOOKUP can also perform multi-criteria lookups in Microsoft 365 and Excel 2021 or later, but INDEX MATCH remains useful in older workbooks and makes the return range and match position explicit. Choose based on the workbook’s Excel versions and the needs of the people maintaining it. For a one-result lookup with several exact conditions, multiplying criteria inside MATCH is direct; for all matching records, use FILTER where available.

Frequently Asked Questions

Can INDEX MATCH use more than two criteria?
Yes. Multiply one comparison for each condition inside MATCH. Every criteria range must have the same dimensions and align row by row with the result range.

Does the formula return the first or last match?
The standard MATCH formula shown here returns the first match from the top. It does not return the last match or list duplicates. Use FILTER to return all matches, or use a separately designed method if the last match is specifically required.

Do I need Ctrl+Shift+Enter for multiple criteria?
Not in Excel for Microsoft 365. Enter the formula with Enter. Older Excel releases may handle array formulas differently, so confirm behavior for the exact version in use.

Can I use INDEX MATCH for OR conditions?
The multiplication pattern means AND: all conditions must be true. OR logic needs a different test because at least one alternative must match; avoid simply multiplying all alternatives together, which would require every alternative to be true.

Why does the formula show #N/A when the values look the same?
Check for extra spaces, different data types such as text versus numbers, and criteria entered in the wrong cells. Also verify that every criteria range covers the same rows and that at least one record satisfies all conditions.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.