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.
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:
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:
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:
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:
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.
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.