As mentioned in title, In this article I will be explaining Excel Index and Match Function. You can use these two function and perform multiple tasks by combining them. These functions can do more advance lookups than a VLOOKUP function which you might have used before. You can perform vertical/horizontal lookups, case sensitive lookups, left lookups and based on criteria lookups as well. So first I will explain each function separately with examples which I believe would be easy for you to understand and use them by combining.
Index function is a very flexible and powerful especially when it comes to advance excel formulas. It actually perform a lookups in given data range and brings a value accordingly. For instance, you have table as shown in below image where you want to get the 4th product code which is “PC1004”. To get the 4th product code, you can use Index function as shown below:
Now let’s say you need to know the product name as per product code. In this case, you need to select whole table, specify the row number as well as column number. As you can see below, I selected the whole table as an array, then 4 is row number and 2 is column number. Once this formula is applied, it will bring the value located in 4 row of 2nd column.
So this is how an Index function works. I know that at this stage, you must be thinking that what if we have a huge number of data? How we are going to find the position of the value? Is there any function to know the values position automatically rather than finding manually?
Of course yes, Excel has a Match function which can be used to locate the values position. Let me first explain the Match function and see how it works.
The main and only usage of excel Match function is to locate the values position in a data sheet. It can also be combined with other function to do an advance lookups such as VLOOKUP, INDEX, functions. Let’s say you want to know the position of Product “HDMI Cable” from the below table. All you need to specify the lookup value, lookup array and lookup type. You will type HDMI Cable as a lookup value, select the lookup array and type 0 for the exact match type as shown below:
Match function can also be used to perform a horizontal lookups as you can see below.
So this is how you can use match function know the position of any value. The last argument ” match type” is important to show the result if you want an exact match so make sure to type 0 if you want an exact match. You can learn more about Match function arguments from Microsoft Support.
Use INDEX & MATCH Function in Excel
As you have learned above how to use Index & Match function separately. Now it will be easy for you to use both functions together. You can use combination of both functions to perform an advance lookups. Let’s go through this function with below examples as it will help you to understand easily.
Let’ say you have a product code and want to lookup for product name. You can use Index & Match function to get the product name. All you need to enter the formula as shown below:
As you can see above that we have used this formula =INDEX(B5:E12,MATCH(B2,B5:B12,0),2).
Lookup Horizontally With INDEX & MATCH Function
You can use Index & Match function to do horizontally lookups as well. Let’s say you have same table in horizontal position where you want to get the same product name associated with product code. All you need to modify the formula as shown in below screen.
As you can see above that we have used this formula =INDEX(C4:J7,2,MATCH(B2,C4:J4,0))
You might be interested to learn more: