Hi, after having discussed the main problem at hand with respect to the data set and with respect to reverse lookup, let's now proceed and see the mechanism. How does this new formula of index work and helps us? So as we noticed earlier that V lookup could not have been using this case, because the common link is placed in the middle and I need an answer from the column before it. So we will apply a formula of index. Now we go slow on this Why? Because this index formula requires some explanation in terms of logic, the first line of parameters that is what you're going to focus upon, it says array.
It doesn't say lookup array or table array, it simply says array, which means you have the flexibility to choose a smaller with one dimension or maybe two dimension. At this moment. We will refer this array as chessboard. Yes, chessboard. If I tell you that look, I have chosen this as my chair. I'm going to fix that.
And the small hypothetical chess board. Now do not consider the chess board to be the ones which you don't normally play. But as a hypothetical scenario, made up of grids, grids of rows and columns, this is our hypothetical chess board. In this chess board, what are we asked is row number. So let me hypothetically provide a number let's say two, comma, and it says column numbers. So I say two.
So in this entire grid, in this entire grid, which is made up of several rows, and three columns, if somebody gives you the coordinates, similar to what we have been using in terms of latitude and longitude. Similarly, if I locate row number two, which is this one, and column number two, which is this one, the intersection of the two is going to point to what cell the cell containing orange and that's what I'm going to get if I press enter. So primarily, this formula of index works like latitude and longitude. It needs the surface area. And it needs the coordinates one, row number, second column number. Okay, that was theory.
But let's see, how do we apply this theory? to fetch our answer? To begin with, let me just ask you to look at this particular picture, which illustrates the point point of what point as in the first parameter of index array refers to the chessboard. I will leave the count of rule number. For example, if somebody asked me, which row number is the company red located in, especially with respect to this blinking border area? That's the third rule.
If somebody would have asked him that is orange, I would have stool second rule. Now how are you coming to this conclusion of second rule being origin third rule being red, because you're counting vertically, top to bottom, so 123, that's four red, blue would be 1234. So the point is, if you've been asked to By index formula row number you are counting vertically. Similarly, if somebody would have asked you please give me the column number for me that you would have certainly told one for a bit, you would have pointed to the third column. Why third because you're counting horizontally or sideways. So the point here is inside the index formula, the third parameter, which talks about column number, you are trying to calculate the sequence in horizontal manner.
So keeping this picture in our mind, Let me place it in the right place. And once I do so, let me proceed with the formula. So here it goes equal to index. array will be the entire table. And you don't need to worry about whether you are going to choose from the first column which is blank, or the first column which has the company name doesn't matter to the entire area. As usual.
Any array, I'm going to fix it f4 comma. Now instead of you doing the counting 123 Evita 1234 mp3, we will delegate this task to who Yes Will delegate this task to match formula. So I'm going to reserve a seat for one match formula in place of row number. The next match formula is going to hold its position where the formula is asking for column number. So both these match formula individually, one will work for rule number one will work for column number. Now, why did I put the dummy placeholders first?
For starters, especially if you're starting this formula for the first time, I would seriously advise that first you dedicate this placeholders so that you avoid any kind of syntax errors. So once the framework is clear in your mind, then we target the first match how do we write the first match formula who is been allotted to find row number? Now, always the first match is going to focus on something which is vertically placed. What is the common link which is vertically placed? If you're thinking abida notice if later point in time if I write EBIT that EBIT would not be shown in the first column. So, I want something which is consistently placed in the first column, second column, third column, basically any one column and that would be what company name.
So, first match look for red comma as we remember from our old v lookup Nash discussions jr follows SR which means index ping the SR formula match will follow its footsteps. So, it will choose the entire array where the common link is placed, but ensure that it doesn't start before nor does it start later. It starts right from where index has begun. And since magical Junior formulate cannot take an Tabler, it has to take a look up. So match please start looking for the word read and start finding the count in this region. Of course, it's going to give you 123 So I'm going to press f4 comma 00 for exact match.
Now targeting the second match, second match always will look for column number and column number can be found if you're trying to find something horizontally. Now what is that common language is pleased horizontally. That's nothing but the financial parameter A B, then EBIT. So match look up for EBITDA, Earnings Before Interest tax, and depreciation, amortization, silica for EBITDA, comma, and start counting from where, not before, not later, Junior follow senior same concept. So it's like a crisscross that is happening on this junction. So after having selected the desired area, I'm going to press f4 comma zero being the standard notation.
The first index, it's capturing the entire chessboard, the next formula match first instance of Inside this index formula will focus on something vertically because it needs to find row number. And the next match formula is going to find something horizontal. As I press enter, notice red abida red on the opposite side, on the reverse side, I have a B does number still fetches me the result. This could not have been done using VLOOKUP and match. So this was just for starters index match match. And we'll practice more of such examples in the next upcoming videos just to quickly compare v lookup match versus index match match.
These are the two pictures illustrations, which will explain the difference between the two. So index, how it works, it is going to focus on the chessboard First, the first junior or the first match will focus on something vertically, wherever it is placed, either first column or second column or third column. The second match will focus on something which is horizontally placed, in this case the header. So there are practically to match formula Whereas in V lookup, we look up the main formula is already trying to find the common link vertically. So hence, we only need one match formula that to find column index number