Index Match Multiple Criteria

Index Match Multiple Criteria - What is it?

Now that you have heard of the Index Match function what is the Index Match Multiple Criteria function and why should this be of any use to you? If you are reading this page, you probably have extolled the virtues of Index Match over V Lookup to your many friends. Or at least those who will listen to you.

However, did you know that you can take the Index Match function even further? That's right! With a combination of array formulas (That's Ctrl Shift & Enter for those who are wondering) and a third argument on the index function then you're up up and away with Index Match multiple criteria.

So what's the problem here? Basically I need to get a sale amount from a list of numbers. Now, ordinarily, I would have a unique identifier for each row or record of my data. Then I could use a V Lookup or preferably the Index Match function to be able to cross reference that unique identifier and bob's your uncle you have the number you wish.

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. Basically 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. That is in the least there are no other rows that have the same Company Name, Contact Name, Order Date and Product date. 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're writing a formula using the Index and Match function that instead of using a unique reference in the match argument, we'll be using multiple criteria to identify the row.

If you just want to know the formula then here you go:

Index Match Multiple Criteria, a little too much?

If you feel you're not ready for this wondering Index Match function, or you feel that you want to know more before you tackle this, here are some Index Match tutorials that might be helpful:

Index Match Tutorial Videos

Index Match Example

Excel Index Match Tutorial

If you want to understand how this Index Match formula uses multiple criteria then read on.

Here enter 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, then 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(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 of 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. Now why should you enter this formula as an array? (This part really only 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 the Match function 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)}

There you have it! As you can see the Index Match Multiple Criteria function is one for your repertoire. However, if you are still struggling to see how this formula can be advantageous to you in your every day life then check out our Using Index Match to Compare Two Lists with Multiple Criteria tutorial.

Of course you don't want to be using multiple criteria in identifying unique rows in your Excel ranges. The reason is that it's all to easy for a duplicate to arise. The you will have to increase the criteria maybe with a greater amount of multiples. Imagine check to see if the company, contact name, product, date, customer, unit amount and more are unique? That would make the multiples in the Match section of the formula crazy complex.

But, if you are forced into it. Or you've yet to decide on a unique identifier then using multiple criteria could be the way forward for you and your spreadsheet.

As mentioned above should you want to follow along with this you need to download the index match multiple criteria exercise files. Here is the completed file. Then you can check if it works in other versions of Excel.

Check out our Using Index Match to compare two lists with multiple criteria tutorial to see a practical way to use this.

Using Index Match to Compare Two Lists with Multiple Criteria