Learn creating formulas using the INDEX and MATCH function to find value in Excel. Avoiding errors thrown using the VLOOUP function.
I hope you will enjoy it; please download the workbook from the below link. In the first worksheet, we will understand the INDEX function. The second is for the MATCH function, and in the third worksheet, we have combined INDEX MATCH two examples. To know how you can use INDEX and MATCH together.
I will try to be quick, If you have any problem, please go back and try to listen again, download the workbook so that it is easy to follow along.
Why error in VLOOKUP formula (limitation)
So before we jump into the INDEX and MATCH function. I want to give a quick understanding of Excel VLOOKUP and why we need the INDEX and MATCH functions.
Although VLOOKUP is an excellent Excel function, there is a very annoying limitation for which we need the INDEX and MATCH. As the VLOOKUP only returns value to the columns to the right (not left) of the data range.
Let’s check with an Example
Let’s understand the VLOOKUP function quickly. Company name CEO and Salary you can see, this is a small dataset, but it applies to a large dataset. That’s not a problem here.
The formula is about to find the salary of this company (Joe) CEO, so what it is that there is a Company, CEO and Salary, this formula is exact lookup value in cell C7 (Company) and the table array selected for C7:E10, and the Column Index is Salary. Whenever you find Column and Row, that means it refers to the selected range.
Now the range lookup is FALSE, which is selected when you want the exact match. So this is fine; this formula returns 100. But the problem is when you want to find the data based on the CEO, once you will select the CEO column, there will be an error.
This error is because you need to have the lookup value (CEO) in the first column (always right side). In this case, it is in the second column. So you have to change the range.
This is C7:E10 to D7:E10, and then again, you have to change the column INDEX number because now this range has only two columns and we want to know the salary of CEO ‘ABC’. So we will change it to second, and then it is correct.
It is not just that you have to change the range lookup also the column number, but you have to adjust the whole formula just because of the limitation of VLOOKUP, in this case.
So to overcome these kinds of constraints, we understand how to use the INDEX and MATCH functions. At the end of the video, you will have an assignment where you will be using INDEX and MATCH to get the salary dynamically.
INDEX - Find value with intersection Row and Column (Array Type)
Now, let’s start with understanding the INDEX function 1st there are four arguments with the INDEX function; I have already input the formula to understand each of them quickly. In the INDEX, we have two types of functions one is an array, and the second is a reference.
We will be working on the array first and then reference. In this case, we have selected C9:J9; this is a selection of one row only, and we want Excel to return the data from the third column.
We have a row number blank because we only have one row in this case. We don’t have multiple rows. And the Excel return the value based on the given column number. Let’s understand the second example where we have a data range with three rows and a column as it is.
We want Excel to return the number based on the column F4, the fourth and third columns. Remember, the INDEX is an intersection between row and column. In this case, the fourth column and third row are the intersections in this selected range, so the fourth and third-row intersection is 34376. And the Excel has returned the same number.
INDEX - Find value with two data range (Reference Type)
Now let’s understand the INDEX function using the reference argument. In this case, there are two references, or you can say area once you give two references Excel first and go and check area number.
In this case, we have area number two. So area number two is C19:D21 that means under Silver membership, and then it goes and checks the row and column number. First, it goes and matches the area number and then looks into the row and column number.
In this case, we have the second area number selected, and it will go and check the third-row number, which is this third-row number, and then it goes and match the column number, which is second. So in this range, there are three rows, and this is two columns. So it goes and selects the third row and second column. So the intersection returns the value 120.
Get the cell position based on value (MATCH)
Now let’s understand the MATCH function. It is relatively easy. The MATCH function returns the cell position number of a value like; in this case, we have selected a range from Part A to Part F. And the Part B is second in this range, and we have chosen the MATCH type.
So, in this case, we have the exact MATCH, and the position of part B is second in the range from B7:B12; if we go and change to Part B to part E, the position is fifth. Similarly, if we see this formula where we have selected a range from C6:F6, the position of 500 is second in this range; if we go and change it to 2000, the position is fourth.
So basically, the MATCH returns the relative position in the given range for that particular value. Until now, we have understood how they can use the INDEX and MATCH functions separately. We will now understand how to merge both of them to find a value based on the row and column.
Merging INDEX and MATCH functions
In this case, we have a discount table. In this table, we have the Part ID and Quantity, and we are trying to find out what the discount will be for a particular part with a specific quantity.
We are using the dropdown, we are selecting our Part ID, and then similarly, we are using Quantity. We can also use a shortcut Key Alt+ Down arrow for the dropdown to show items on the list. In this case, part B is in the second position, and 2000 is in the fourth position.
Now we will see what the intersection of Part B and 2000 (Quantity) is. That is the second row and fourth column with the INDEX function. For the INDEX function, we have this dataset selected. This is the array, this is the row number that is second and D17 which is column number, row number part B second and D17 is quantity 2000.
And the intersection is the value, which is return by Excel in cell D20. If we change it to part E, automatically, Excel changes the return value to 17 percent.
So this is the intersection. We can also replace the cell reference of D16 and D17 directly with the formula. Just copy the formula and paste it here. Now, the MATCH formula is directly a part of the argument of the INDEX function. Likewise, we can do for D17 cell reference as well.
Now, let’s understand, using the INDEX reference type function with MATCH function in this example 2. We will understand the INDEX using the reference function if you remember our first example of INDEX here, in which we used reference.
In this example, we will use the same, but with INDEX and MATCH together. There are two types of pricing: Winter and Off Season, three types of products & two kinds of membership. Based on that information, we need to find the pricing; in this case, we have created a dropdown for Membership, Product, and Season. If we try to find it with this small dataset manually, we will see the silver membership the product is C. And the season is winter. So this 120 Excel is returning the correct price.
So basically, now we have a formula of MATCH. The position of product C is the third is the range. Similarly, for winter, the place is first in this range. Now we will add both of these rows and columns with the INDEX function. Here we have two references, and for area numbers, I have used the IFS function, so if it is silver, it will return two, and if it is gold, it will return one. You can verify by selecting the formula and click the F9 shortcut, and you can see this is referring to area number two.
In this case, which is C29:E31, and then we directly add row number and column number. In this case, the column number is plus one because we have only two options in this list. But if you will see the range, we have three columns. So if we want to input an argument for the INDEX function, we have to add one, considering there are only two seasons so that Excel can find the information based on the third column. Like now, we can change the season, we can change the product, and everything is dynamic.
As you can see now, this MATCH function is directly linked with INDEX and returning the value appropriately. Remember, it is essential to understand the formula’s logic because the data presented here will be different from the real-life example.
So if you want to understand the INDEX and MATCH function, try to do this, exercise yourself by changing some structure of data presentation, and see how the INDEX and MATCH function is working. Now, it’s assignment time.
I believe you guys have learned something on INDEX and MATCH here. It’s critical to do your assignment and create a formula based on the INDEX & MATCH on your own. Don’t try to look into the comments on any other shortcut. Try to build the INDEX and MATCH formula on your own. To give you a quick understanding of how it will work. There is one dependent drop down here, so if you select CEO, then all the information based on CEO will populate in the secondary dropdown, and then the salary amount will so here.