Hi, this is going to be a practice task on index match match something that is quite often used for reverse lookup. Before we begin with the exercise, just a quick recap with the methodology that we were trying to apply index match match in index. The first parameter of the formula asks for array selection which we had referred by the term chess board mini chess board. The second two parameters talks about coordinates rule number first followed by column number, row number will be calculated vertically 123, top to bottom and column number will be calculated horizontally or sideways that is 123. Now the data structure would look like this. This will be the full chessboard the first match will focus on one column either this or this or this basically any one column where the common link is present.
And the second match will focus on horizontal or sideways movement now Keeping that in mind, let's look at this particular task. The table on the left, we have a table of employee IDs. That's here. The names are on the left hand side of the ID codes and gender age followed on the right hand side. Now looking at the ID codes, I want us to fetch the name. This could not have been done using v lookup, unless of course, you choose to cut this particular column and place it in the first column of the table array.
But assuming we are not able to do so we will stick to index match match equal to index. The first parameter array, which is the first chessboard let me choose the entire chessboard. This is our terminology in the discussion. Now, after having chosen the array, I'm going to press f4 to fix it, comma. Now I need to coordinate 14014 column number so I'm going to dedicate to match formulas one after another Now reserving a seat for each of the to match formula helps me avoid the syntax errors later point in time. Let me now focus on the first match.
The first match is going to look for an array which is lying vertically, top to bottom, and that is the ID code column within which what is the common link that's the ID code. So, match please look for 876 comma next will be lookup array, which means Junior follow senior the same principle which we had referred to in our earlier videos, that is to not start from before the start of the table or a selection of the index or later, it will start from the boundaries of the array selection. Now, making sure that it is fixed two comma zero to make sure it is an exact match that the match formula is trying to look for. next match formula look for any term that you are able to find in the header. So basically, you cannot look for 876 because 876 cannot be found in the header.
What can be found is the word name. Now, I'll have to fix this though I don't want this to move down as a copy paste the formula. So I'm going to fix this, comma. Next we'll be choosing the lookup array which this time is going to refer to a sideways movement. So the same concept applies Junior follow senior, not starting before, not starting later. and choosing from mobile number till the last cell value each.
I'm going to fix that to a four comma zero. Let me test whether the formula works in perfect. In fact, let me copy paste and see whether it works for the remaining two. Oh, yes, it does. In fact, when I change the header, let's say gender I get the gender as an answer each. There you go.
While number definitely correct. Now just an add on to this exercise, assume that if I press Ctrl R, at this instance, it quickly copies Paste the cell on the right. And if I choose name, I would want the formula which we wrote is equally applicable as a copy paste it right and down. But it doesn't. Let us discuss why. When I double click on the any, the first few things that I will notice on the screen is the colored borders.
The blue colored border, which is right now highlighted indicates the area of the chessboard or index. The purple one is indicating the first match lookup array, and the pink one is indicating the lookup array of the second match. Now what we also notice is these two lookup values, the first one actually has traveled from this cell to the next one, since it was not fixed. On the other hand, this particular cell mobile number was fixed Hence, it is not able to move to the next cell. So we need to rectify two things. One, we go back to the main formula.
First things first, this seafood I need to ensure that It remains in column C, but at the same time, it should be able to move to five and six. So I put my cursor around C four, I keep on pressing f4 until I get dollar C, followed by four, no dollars before four. This ensures that column C remains in column C. Next, D three, I want to ensure that when I copy paste the formula towards the right inside this d3 should be able to jump to E three. Hence, for that to happen, I need to remove the dollar before B, four. There you go. Now you might ask me, why am I putting the dollar before three, because I don't want that the match lookup value, which is currently pointing to the value in the cell of third row should be able to move down to fourth or fifth or six.
So be very careful. There's $1 c four, and this is the dollar three. Now let me just select the cell. All the array where I need to copy paste the formula. I'll press Ctrl D Ctrl r Hmm, now we get to perfect answer. And this was index match match for reverse lookup