Computer Tutoring Logo
Est. 2002
Menu Button

Index Match Multiple Criteria

Share

What is Index Match?

If you've been using Excel for a while your most likely have come across VLookup. If you're a dab hand at VLookup then you probably want to move on to Index Match. See why Index Match is better than VLookup.

Index Match with Multiple Criteria

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 virtures of Index Match over VLookup 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 arguement 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 VLookup 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. Take a look below:

Excel Index Match Multiple Criteria laying out the basics

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. However, I am assuming that if the Company Name, Contact Name, Order Date and Product are all the same then the likelyhood is that that sales is a match. So you can see from the above spreadsheet that the coloured columns are the columns I would need to lookup multiple criteria to ensure that I've selected the correct row.

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

Index Match Multiple Criteria formula

Index Match Multiple Critieria, 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


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 Mutliple Critieria Step by Step

  1. First things first and that is you might want to download the same list of data that I'm using. This will make things a lot easier. That is working with the exact same data that I have been using. Then your progression will be that little bit easier.
  2. Once you open the 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 arguement 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. 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,
  9. It's time to start entering in the conditions you can do that by entering each condition within brackets seperated by asterisks.
  10. Type (A:A=L1) which basically means it 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.
  11. 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)
  12. Enter a comma, then enter the last argument for the MATCH function which will be 0. This is because you want the formula to return an exact value.
  13. Close of the MATCH function brackets by typing ).
  14. 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)
  15. Now comes the array formula part of the function. Now why should you enter this formula as an array? Well 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.
  16. 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. Hopefully this function will help you out a lot of scapes.

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.