# Excel INDEX MATCH with Multiple Criteria: Step by Step

Excel INDEX MATCH with multiple criteria lets you look up a value when one key is not enough to identify the row you want. You multiply two or more comparison arrays inside MATCH, force an exact match, and feed the resulting row number into INDEX. This article shows the exact formula, a worked example, and the errors that trip people up.

## Quick Answer

- The core pattern is `=INDEX(return_range,MATCH(1,(criteria1_range=criteria1)*(criteria2_range=criteria2),0))`.
- Each comparison like `(A2:A7=E2)` produces an array of TRUE and FALSE values.
- Multiplying those arrays turns TRUE into 1 and FALSE into 0, so only rows matching every criterion produce a 1.
- MATCH looks for the number 1 with match_type 0, which forces an exact match [1].
- INDEX then returns the value at that row position in your return range [2].

## Before You Start

You need three things in place before writing the formula.

First, your data should be in a clean table or range with one header row and no blank rows inside the data. Blank rows break the alignment between your criteria ranges and your return range.

Second, all criteria ranges must be the same size and shape. If `A2:A7` has 6 cells, then `B2:B7` and `C2:C7` must also have 6 cells. Mismatched sizes cause wrong results or errors.

Third, decide whether you need an exact match. For text criteria and most business lookups, you do. The method below always uses exact matching.

If you are new to the individual pieces, read the [INDEX function syntax and examples](/blog/data-analysis/excel-index-function-syntax-examples) and the [MATCH function syntax](/blog/data-analysis/excel-match-function-syntax) first. The combination is easier to follow once each function makes sense on its own.

One version note. In Excel for Microsoft 365, Excel 2024, and Excel 2021, the array formula works when entered normally in most cases. In older versions you may need to confirm it with Ctrl+Shift+Enter so Excel treats it as an array formula. Microsoft's own documentation on correcting #N/A errors in INDEX/MATCH covers the array behavior [3].

## Step by Step

1. **Identify your return range.** This is the column holding the value you want back, for example `C2:C7` for Units.

2. **Identify your criteria ranges.** These are the columns you filter on, for example `A2:A7` for Region and `B2:B7` for Product.

3. **Write each comparison.** `(A2:A7=E2)` tests the Region column against the value in E2. `(B2:B7=F2)` tests the Product column against F2.

4. **Multiply the comparisons.** `(A2:A7=E2)*(B2:B7=F2)` returns 1 only where both are TRUE.

5. **Wrap it in MATCH.** `MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)` finds the position of the first row where the product equals 1 [1].

6. **Wrap that in INDEX.** `INDEX(C2:C7,MATCH(...))` returns the value from the return range at that position [2].

7. **Confirm the formula.** In older Excel versions, press Ctrl+Shift+Enter. In current versions, Enter usually works.

The full formula is:

$$=\text{INDEX}(C2:C7,\text{MATCH}(1,(A2:A7=E2)*(B2:B7=F2),0))$$

For three criteria, add another multiplication term:

$$=\text{INDEX}(D2:D7,\text{MATCH}(1,(A2:A7=E2)*(B2:B7=F2)*(C2:C7=G2),0))$$

Each extra criterion is one more `(range=value)` block joined with `*`.

## Worked Example

The dataset below records units sold by region and product. You want the Units value where Region is East and Product is Widget.

|   | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Units |   | Region | Product | Units |
| 2 | East | Widget | 120 |   | East | Widget | `=INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0))` |
| 3 | East | Gadget | 95 |   |   |   |   |
| 4 | West | Widget | 140 |   |   |   |   |
| 5 | West | Gadget | 110 |   |   |   |   |
| 6 | East | Widget | 130 |   |   |   |   |
| 7 | North | Widget | 105 |   |   |   |   |

The formula in G2 returns **120**.

Here is why. The Region test `(A2:A7=E2)` marks rows 2, 3, and 6 as TRUE because they are East. The Product test `(B2:B7=F2)` marks rows 2, 4, 6, and 7 as TRUE because they are Widget. Multiplying the two arrays gives 1 only at rows 2 and 6, the rows that are both East and Widget. MATCH finds the first 1 at position 1, and INDEX returns the first value in `C2:C7`, which is 120.

Notice that row 6 is also East and Widget with 130 units. The formula returns the first match, not a sum. If you need to total all matching rows, use SUMIFS instead.

## Other Ways to Do It

**XLOOKUP with concatenated keys.** You can join criteria into a single key with `&` and look that up. This works but requires helper columns or array concatenation.

**INDEX with XMATCH.** XMATCH is the newer function and returns exact matches by default, which removes the need to type the 0 match_type [4]. A two-way lookup mixing INDEX and XMATCH looks like `=INDEX(C6:E12,XMATCH(B3,B6:B12),XMATCH(C3,C5:E5))` [4].

**SUMIFS or COUNTIFS.** If you want a total or a count across all matching rows instead of a single value, these are simpler and faster.

**FILTER.** In Microsoft 365, FILTER returns every matching row at once. See [how to filter in Excel](/blog/data-analysis/how-to-filter-in-excel) for the manual approach and the dynamic array approach.

For a broader comparison of lookup tools, see [Excel lookup functions: VLOOKUP, XLOOKUP and INDEX-MATCH](/blog/data-analysis/excel-lookup-functions). If you are coming from single-criterion lookups, start with [Excel INDEX MATCH step by step](/blog/data-analysis/excel-index-match).

## Troubleshooting

**#N/A error.** MATCH did not find a 1, which means no row matched all criteria [3]. Check for trailing spaces, numbers stored as text, or a typo in a criterion cell.

**Wrong value returned.** You likely have a size mismatch between ranges, or the first matching row is not the one you expected. Remember that MATCH returns the first match.

**#VALUE! error.** This often means the array multiplication produced something MATCH cannot interpret, or the formula was not entered as an array in an older version.

**Formula returns 0.** A zero can be a real value in your data, so check whether the matched row genuinely holds 0.

**Criteria in the wrong order.** The order of the multiplication terms does not matter for correctness, but the order of ranges inside INDEX does. Make sure the return range lines up with the same rows as your criteria ranges.

## Common Mistakes

- **Using commas instead of multiplication.** `MATCH(1,(A2:A7=E2),(B2:B7=F2),0)` is invalid. Join the comparisons with `*`, not commas.
- **Forgetting the 0 in MATCH.** Without match_type 0, MATCH may return an approximate position and a wrong value [1]. Always use 0 for exact matching.
- **Mismatched range sizes.** If `A2:A7` is 6 rows but `C2:C8` is 7, results shift. Keep every range the same height.
- **Expecting a sum.** INDEX MATCH returns one value. Use SUMIFS when several rows match and you want them added.
- **Leaving spaces in criteria.** `"East "` with a trailing space will not match `"East"`. Trim your lookup values.
- **Not entering as an array in older Excel.** In pre-dynamic-array versions, press Ctrl+Shift+Enter or the formula may fail [3].

## Limitations

INDEX MATCH with multiple criteria returns a single value from the first matching row. It cannot return a list of all matches, and it cannot sum or average them. If your data has duplicate combinations, you get the first one only, which can mislead you into thinking there is a unique record when there is not.

The array multiplication approach also becomes harder to read as criteria grow. With five or six conditions, the formula gets long and error-prone. In those cases, helper columns that concatenate keys, or functions like SUMIFS, FILTER, and XLOOKUP, are usually clearer and easier to audit. Performance can also suffer on very large ranges because the array operations evaluate every row.

## Frequently Asked Questions

### Can INDEX MATCH handle more than two criteria?

Yes. Add one more `(range=value)` block for each extra criterion and join them all with `*`. The logic is identical. Three criteria look like `MATCH(1,(A2:A7=E2)*(B2:B7=F2)*(C2:C7=G2),0)`. Keep every range the same size.

### Why does my INDEX MATCH with multiple criteria return #N/A?

MATCH could not find a row where all criteria were true [3]. Common causes are trailing spaces, mismatched data types such as text stored as numbers, or a criterion that simply does not exist in the data. Verify each criterion separately before combining them.

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

In Excel for Microsoft 365, Excel 2024, and Excel 2021, you can usually press Enter. In older versions, the formula may need Ctrl+Shift+Enter to work as an array formula [3]. If you see unexpected errors, try the array entry.

### Is XLOOKUP better than INDEX MATCH for multiple criteria?

XLOOKUP is newer and returns exact matches by default, which removes one common error source [4]. It still needs concatenated keys or nested logic for multiple criteria. INDEX MATCH remains widely used and works in every modern version, so either is a reasonable choice.

### Can I return a value from a column to the left of my criteria?

Yes. This is a key advantage over VLOOKUP. INDEX takes any return range, so the return column can sit to the left, right, or anywhere in the sheet [2]. Just make sure the return range has the same number of rows as your criteria ranges.

## References

1. [MATCH function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/match-function)
2. [INDEX function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/index-function)
3. [How to correct a #N/A error in INDEX/MATCH functions | Microsoft Support](https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error-in-index-match-functions)
4. [XMATCH function | Microsoft Support](https://support.microsoft.com/en-us/excel/functions/xmatch-function)

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)

## Related Articles

- [Excel INDEX MATCH: How to Look Up Values Step by Step](/blog/data-analysis/excel-index-match)
- [Excel MATCH Function: Syntax and Examples](/blog/data-analysis/excel-match-function-syntax)
- [Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH](/blog/data-analysis/excel-lookup-functions)
- [How to Filter in Excel (Step by Step)](/blog/data-analysis/how-to-filter-in-excel)
- [Excel INDEX Function: Syntax, Examples and How to Use It](/blog/data-analysis/excel-index-function-syntax-examples)