INDEX MATCH with Multiple Criteria in Excel

Quick answer: INDEX MATCH with multiple criteria lets you return a value only when several conditions are true, such as matching a company, contact, order date and product in the same row. It is useful for sales reports, customer lists, reconciliations and other workplace spreadsheets where one column on its own is not unique.

Updated May 2026: The formula on this page still works in Excel 365, Excel 2021 and older desktop versions. In current Excel 365 you can usually press Enter; in older versions you may need Ctrl + Shift + Enter to confirm it as an array formula.

Now that you have heard of the INDEX MATCH function, what is the INDEX MATCH multiple criteria formula and why should this be of any use to you? If you are reading this page, you may already know why INDEX MATCH can be more flexible than VLOOKUP in Excel.

However, you can take the INDEX MATCH function even further. With a combination of logical tests and a third argument in the INDEX function, INDEX MATCH can identify the correct record using more than one condition.

So what's the problem here? Basically I need to get a sale amount from a list of numbers. Ordinarily, I would have a unique identifier for each row or record of my data. Then I could use VLOOKUP, or preferably the INDEX MATCH function, to cross reference that unique identifier and return the number I need.

OK! I hear you shout. So what's the issue here? The issue is simply that I do not have a unique identifier for each sale. There is no one particular column that I can use to ensure that I am seeing the correct figure, so I have to use multiple criteria. What does it mean to use multiple criteria? Let's assume that the Company Name, Contact Name, Order Date and Product create a unique record. In other words, there are no other rows with the same Company Name, Contact Name, Order Date and Product. These are the multiple values that we are speaking of.

INDEX MATCH with multiple criteria will use these values to pick out that unique row. Basically, we are writing a formula using the INDEX and MATCH functions that, instead of using a unique reference in the MATCH argument, uses several criteria to identify the row.

If you just want to know the formula pattern, here it is:

=INDEX(return_range,MATCH(1,(criteria_range1=criteria1)*(criteria_range2=criteria2)*(criteria_range3=criteria3),0),column_number)

INDEX MATCH Multiple Criteria Formula at a Glance

In Excel 365 this pattern is often easier to replace with XLOOKUP, FILTER or a helper column. INDEX MATCH is still useful when you are maintaining older workbooks, matching several fields together or working in a file where colleagues already understand the INDEX MATCH pattern.

Which INDEX MATCH Multiple Criteria Tutorial Do You Need?

INDEX MATCH Multiple Criteria, a Little Too Much?

If you feel you're not ready for this INDEX MATCH formula, or you want to know more before you tackle it, here are some INDEX MATCH tutorials that might be helpful:

INDEX MATCH Tutorial Videos

If you want to understand how this INDEX MATCH formula uses multiple criteria then read on. The same logic is useful when preparing management reports, checking order data or matching records exported from different systems.

Here comes the power of the array formula and the solution to this thorny problem of INDEX MATCH multiple criteria in Excel. Here's what you do:

INDEX MATCH Multiple Criteria Step by Step

So, now you want to know how to do INDEX MATCH with multiple criteria. Here's how:

1. First things first, download the index match multiple criteria data file that I'm using. This will make things a lot easier.
2. Once you open the index match multiple criteria Excel spreadsheet, click in cell L6. That's where you'll be typing the INDEX MATCH array formula.
3. Type in =INDEX(
4. Now, using the mouse, select Columns A to H. This will form the first argument in the INDEX function. See INDEX function training video for more details about this helpful Excel function.
5. Now your function should look like: =INDEX(A:H
6. Next, type in a Comma and enter MATCH( to start the Match part of your formula.
7. Now, type in 1. The reason for this is you want the Match function to return a true if any particular row meets all of the criteria that you're about to enter.
8. So far your formula should read =INDEX(A:H,MATCH(1,
   Alright, the next thing you need to do is to start entering conditions. These conditions are entered in the second argument of the Match function, this is the multiple criteria part of the formula. You enter each condition within brackets separated by asterisks.
9. First, after the comma in the formula in step 8, type (A:A=L1). This condition will return a true value if anything in the A:A column is equal to the value in cell L1. Or for this example Fabulous Book Store.
10. Fill in the rest of the formula remembering to use the asterisk to link together the logical conditions. Your formula so far should look like: =INDEX(A:H,MATCH(1,(A:A=L1)*(B:B=L2)*(C:C=L3)*(D:D=L4)
11. Enter a comma, then enter the last argument for the MATCH function which will be 0. This is because you want the Match function to return an exact value.
12. Close off the MATCH function brackets by typing )
13. Add in another comma and then type 8 followed by a bracket to finish the formula. Your formula should now look like:
    =INDEX(A:H,MATCH(1,(A:A=L1)*(B:B=L2)*(C:C=L3)*(D:D=L4),0),8)
    Now comes the array formula part of the INDEX MATCH multiple criteria formula. Why should you enter this formula as an array? This part mainly applies to older versions of Excel. If you have a current version of Excel 365 you don't need to hold down Ctrl + Shift + Enter. You just need to press Enter.
    But if you have an older version of Excel then you will need to hold down Ctrl + Shift + Enter. Because if you just press enter you will get an error. This is because the INDEX and MATCH functions can only look at one cell at a time, and return a result accordingly. But when you hold down the control and shift keys as you press enter you tell Excel to take into account the entire range instead of just one cell.
14. Hold down Ctrl & Shift then press Enter to enter the formula as an array.

Your function should now return the value of £299.50. Also if you look close you can see the braces, also known as the curly brackets encompassing your formula.
{=INDEX(A:H,MATCH(1,(A:A=L1)*(B:B=L2)*(C:C=L3)*(D:D=L4),0),8)}

When Should You Use INDEX MATCH with Multiple Criteria?

Use this approach when a single lookup column is not enough to identify the correct record. In a workplace workbook, that might mean matching customer, order date and product together before returning a sales value, invoice amount or status. If your data already has a reliable order number or transaction ID, use that unique identifier instead because it will be easier to audit later.

• Use it for reconciliations where exported lists do not share one clean ID column.
• Use it for sales, finance and operations reports where several fields define one record.
• Consider XLOOKUP, VLOOKUP and INDEX MATCH options when deciding which lookup method is clearest for your team.

There you have it! As you can see the INDEX MATCH multiple criteria formula is one for your repertoire. However, if you are still struggling to see how this formula can be useful in everyday work then check out our Using INDEX MATCH to Compare Two Lists with Multiple Criteria tutorial.

Of course, it is usually better to have a reliable unique identifier in your Excel data. Multiple criteria formulas can become harder to maintain if you have to check company, contact name, product, date, customer, unit amount and more. For cleaner long-term reporting, consider adding an order number, transaction ID or helper column where possible.

But, if you are forced into it, or you have not yet decided on a unique identifier, using multiple criteria could be the way forward for you and your spreadsheet. It is a practical technique for finance, operations, admin and sales teams who need to find the right record without manually filtering a table.