How to Use the INDEX MATCH Functions with Multiple Criteria

The INDEX MATCH with multiple criteria formula in Excel is a powerful way to look up a value when more than one condition must be true. Instead of searching by only one lookup value, this formula lets you match several fields at the same time, such as region, sales rep, and product. It is especially useful for business reports, sales tracking, inventory lists, employee records, and any spreadsheet where one condition is not enough to find the correct result.

In This Guide, You’ll Learn

• What INDEX MATCH with multiple criteria does
• How the INDEX and MATCH functions work together
• How to write a multiple-criteria lookup formula
• How to use INDEX MATCH with sales, budget, and inventory examples
• Common mistakes to avoid when using multiple criteria
• Practical workplace uses for INDEX MATCH with multiple criteria

What is the INDEX MATCH with Multiple Criteria Function?

INDEX MATCH with multiple criteria is a lookup formula that returns a value from a table when several conditions are met at the same time.

INDEX MATCH with Multiple Criteria Function Syntax

=INDEX(return_range,MATCH(1,(criteria1=range1)*(criteria2=range2),0))

Argument Definitions

• return_range: The range that contains the value you want Excel to return.
• MATCH(1,…): Tells Excel to find the row where all criteria are true.
• criteria1: The first value you want to match.
• range1: The column or range where Excel should look for the first criterion.
• criteria2: The second value you want to match.
• range2: The column or range where Excel should look for the second criterion.
• 0: Tells MATCH to find an exact match.

The basic idea is that each condition creates a TRUE or FALSE result. When the conditions are multiplied together, TRUE values act like 1 and FALSE values act like 0. Excel then looks for the row where the final result equals 1, meaning every condition was met.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> INDEX MATCH with Multiple Criteria Function Data

How INDEX MATCH with Multiple Criteria Works

A normal INDEX MATCH formula usually looks up one value. For example, you might look up a product name and return the price.

But in many real spreadsheets, one lookup value is not enough. You may have the same product sold in multiple regions, by multiple sales reps, or on multiple dates. In those cases, you need Excel to check more than one condition before returning the result.

That is where INDEX MATCH with multiple criteria becomes useful.

Instead of saying, “Find the product,” the formula can say, “Find the row where the region is East and the product is Laptop.”

Look Up Revenue by Region and Product

In this example, we want to return the revenue for a specific region and product.

Formula Used

=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))

How This Formula Works

• INDEX(C2:C6,…) tells Excel to return a value from the Revenue column.
• A2:A6=D2 checks which rows match the lookup region.
• B2:B6=E2 checks which rows match the lookup product.
• The multiplication symbol combines both conditions.
• MATCH(1,…) finds the first row where both conditions are true.
• Excel returns 12500 because the row with Region East and Product Laptop has revenue of 12500.

Look Up Profit by Sales Rep and Product

In this example, we want to return the profit for a specific sales rep and product.

Formula Used

=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))

How This Formula Works

• This formula looks for the row where the sales rep is Brown and the product is Laptop.
• Once Excel finds that row, it returns the matching profit from column C.
• The result is 2750.

Look Up Budget Amount by Department and Month

INDEX MATCH with multiple criteria is also useful for budget reports. In this example, we want to return the budget amount for a specific department and month.

Formula Used

=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))

How This Formula Works

• The formula checks the Department column for Operations.
• It also checks the Month column for February.
• Only one row meets both conditions, so Excel returns the budget amount from that row.
• The result is 11200.

This type of lookup is helpful when building monthly budget summaries, department dashboards, or financial reports.

INDEX MATCH with Three Criteria

You can also use INDEX MATCH with more than two criteria. In this example, we want to look up revenue based on region, sales rep, and product.

Formula Used

=INDEX(D2:D6,MATCH(1,(A2:A6=F2)*(B2:B6=G2)*(C2:C6=H2),0))

How This Formula Works

• The region must be East.
• The sales rep must be Adams.
• The product must be Laptop.
• When all three conditions are true on the same row, Excel returns the revenue from column D.
• The result is 12500.

This is a great formula for larger reports where records may repeat across several columns.

Using IFERROR with INDEX MATCH Multiple Criteria

In this example, the lookup product does not exist for the selected region, so we use IFERROR to display a friendlier message.

Formula Used

=IFERROR(INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0)),"Not Found")

How This Formula Works

• This formula works the same way as the earlier examples, but IFERROR catches the error if no match is found.
• Instead of showing an Excel error, the formula returns Not Found.

Important Note About Older Versions of Excel

In older versions of Excel, INDEX MATCH with multiple criteria may need to be entered as an array formula.

After typing the formula, press:

Ctrl + Shift + Enter

instead of only pressing Enter.

In newer versions of Excel, including Microsoft 365 and Excel 2021, you can usually press Enter normally.

Why Use INDEX MATCH with Multiple Criteria Instead of VLOOKUP?

VLOOKUP is useful, but it usually looks up one value in the first column of a table. That can be limiting when your data requires more than one condition.

INDEX MATCH with multiple criteria is more flexible because it can:

• Look up values using two or more conditions
• Return values from columns to the left or right
• Work with repeated names, products, dates, or departments
• Handle more complex business reports
• Avoid creating helper columns in many situations

For example, if the same product appears in several regions, VLOOKUP may return the first match it finds. INDEX MATCH with multiple criteria can return the correct value by checking both the product and region together.

Common Mistakes with INDEX MATCH and Multiple Criteria

Mismatched Ranges

One common mistake is using ranges of different sizes. The return range and criteria ranges should cover the same number of rows.

For example, this is not correct:

=INDEX(C2:C10,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))

The return range has more rows than the criteria ranges.

A better formula would be:

=INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0))

Forgetting Exact Match

Another common mistake is forgetting the exact match argument in MATCH. The final argument should usually be 0.

MATCH(1,(A2:A6=E2)*(B2:B6=F2),0)

You may also get an error if no row matches all criteria. In that case, you can wrap the formula in IFERROR.

=IFERROR(INDEX(C2:C6,MATCH(1,(A2:A6=E2)*(B2:B6=F2),0)),"Not Found")

This returns Not Found instead of an error message when Excel cannot find a matching row.

When to Use INDEX MATCH with Multiple Criteria

INDEX MATCH with multiple criteria is useful when one lookup value is not specific enough.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> INDEX MATCH with Multiple Criteria Function Data

Related Articles