Multiple criteria or if statements with Index Match
Now you may have heard of the Index Match function! In fact you possibly have played around with the function a little bit. 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 away.
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.
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. And, also I don't want one. Say my boss doesn't want me to change the data in any way. Also I don't want the hassle of copying this data to another sheet simply to add another column, to which I can add the ID and hence do this with the Index Match. So what do I do?
Here enter the power of the array formula and the solution to this thorny problem. Here's what you do:
- 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.
- Once you open the Excel spreadsheet then click in cell L6 That's where you'll be typing the index match array formula.
- Type in =INDEX(
- 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.
- Now your function should look like: =INDEX(A:H
- Type in a Comma and enter MATCH( to start the Match part of your formula.
- 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.
- So far your formula should read =INDEX(MATCH(1,
- It's time to start entering in the conditions you can do that by entering each condition within brackets seperated by asterisks.
- 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.
- 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)
- 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.
- Close of the MATCH function brackets by typing ).
- Add in another comma and then type 8 followed by a bracket to finish the formula. Your formula should now look like:
- 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 Idex 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.
- 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.
There you have it. Hopefully this function will help you out a lot of scapes.