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:
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:
So, now you want to know how to do index match with multiple criteria. Here's how:
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! 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.
We do not store your credit/debit card details. You have the right to access, amend, or request deletion of your personal data by contacting us at firstname.lastname@example.org.