Mohammad Khalid

let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given.

Now, we have this small table in front of us in which we have a list of expenses as well as for which particular column we need to find out the value for. First we need to find out the code for grocery, home phone, cable TV and tuition. Same we need to find out the expenses related to these expenses, these items in quarter 2, 3 and 4. If you see one thing that we are talking about code right now.

If you look at it, expenses are on column D and technically this is the third column in the data and code is the second column. So, this is basically a reverse pre-lookup or reverse lookup we are talking about. Now here, first I am going to show you that what INDEX do.

You know about the MATCH already. The MATCH function is that if I assign a MATCH function, just have a look. First I am going to break down the formula for you to make it easier for you to understand and then we will club it in a single cell.

So have a look. I am going to use MATCH first and then we will club it with INDEX. So, I am just creating a small table here which is grocery and code.

Now what is the position of grocery in this range? I do not know about it. So, in that case as you know we are going to use MATCH. Lookup value will be grocery comma lookup array that in which column you need to find out, you need to match this grocery word.

So I am going to click on this and I am going to say control shift down arrow and then select it till the end. I will press F4 which is somehow not required because we need not to copy and paste it somewhere comma MATCH type will be zero. So I will close down the bracket now and when I press enter, see this.

It is coming at the third position from expenses. Same thing I need to find out that where this code word is coming in this complete heading. So, I am going to say equals to MATCH lookup value will be code comma lookup array will be from this cell control shift right arrow till the end comma zero.

Bracket close and enter. See this. It is telling us that it is coming at the second position.

So, grocery and code the position are there with us. Now we need to find out the value which is the code for grocery. This is what we need to do here.

So we have two matches over here and now we are going to apply index. So index is a very simple formula guys. If you know the column number like this and row number in that case you just need to assign the complete database and you need to put the row number as well as column number in index formula and it will give you the cross value out of that.

I will show you equals to index concentrate on the first syntax which is array with row number and column number. So array is the complete range which is this cell then control shift down arrow control shift right arrow. So, I will freeze it comma now it is asking for a row number through grocery we found out the row number.

The row number for the grocery is three comma column number is this two which column we are talking about that is a code column. So bracket close and enter. See this.

The code is coming which is RG S01 O2. So that is the grocery the code for the grocery is the same that we are getting the answer. I hope this is clear to you a bit and I would really suggest you do a lot of practice on this.

Now let me club this over here and then we can copy and paste it to what's right and down and we'll get all the values for this matrix. So I'm going to say equals to index array means the complete database that I have told you and I really want you to do one thing. I really suggest you to always select it from the very first cell that must be the heading.

So it should not confuse by the counting. So I'll select it by pressing control shift down arrow as well as control shift right arrow. I'll freeze it because I need to copy and paste it down by pressing F4 key on my keyboard function F4 key comma.

Now I do not know the row number. So I'm going to say I'm going to put match tab. Now look up value for this match.

Remember this. We are standing on row number right now. Just have a row number.

You cannot shuffle this because whatever the syntax is you have to follow that exactly like that. So I'm going to say first match and I'm going to find out the row number using this match. So look up value will be grocery comma look up array will be this expenses one because we know that the grocery home phone and all the things are here in this column and representing a particular row.

So that is where this is going to be our row number. So control shift down arrow and we'll select it till the end and I'm going to press F4 again comma zero then we'll close on the bracket for the first match and we'll get the row number now and as I told you earlier also we have a shortcut to see that what would be the value of this match so you can actually select it like this. So I've selected it and now if I press F9 function F9 key on my keyboard it will tell me the result which is three and you can see it as correct that what we will get.

Now I'll have to press control Z so that will show me the formula again now column number. So column number is basically the code right now code column. So for that again we have to put match tab now the lookup value for this match will be this word code comma lookup array will be this complete range from here till control shift right arrow till here.

So I'm going to freeze it by pressing F4 key on my keyboard comma zero. So first bracket will close down for match and the other one is for index. So we are done with our formula now.

So you can see one index and two matches in between. One thing we need to freeze in this B7 which is this cell as I have discussed earlier this kind of freezing and we look up and match video so you can refer that and here we are going to freeze or put a dollar sign just before B because I need to copy and paste it towards right and I don't want to change the color. And over here C6 I need to freeze the row number six.

So I'm going to put a dollar sign just before six. So when I copy and paste it towards down it should not change the row. So now if I press enter we are getting the result which is RGS0102 that's one.

And now when I copy it and I press shift and right arrow and down arrow and when I press enter you see that we are getting the results for all. So this is how it's a two way lookup. It is one of the most useful combination in lookup functionality.

I would really prefer that you should use index and match anywhere in your work wherever you are using VLOOKUP because somehow whatever VLOOKUP can do index and match can easily do as well as something which VLOOKUP cannot do can be done using index and match. So that is this is kind of an ultimate formula in lookup functionality. So I hope you enjoyed this video.

I again I'm saying that you need to do some practice. I know we are going ahead and it is kind of a complex function but it really needs some practice and you'll feel very much comfortable when you do it couple of times. That is why in this course I have created two exercise files for this particular function so that you can get a quick hands on on that.

So just open the next file which is exercise 0613 index and match and will do it and it has two part part one and part two. So it has two examples. So you have ample of exercises for you to do it.

Thank you.

Step 1-Select the table including the heading 

2. Click on Insert Tab

3. Recommended Charts

4. All charts 

5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK.

If you want to learn more, book a Demo class with me. Thanks.