In this tutorial you will learn how to use the Index and Match Function in Microsoft Excel.
Index and Match is the very powerful lookup function. This is a great alternative to VLOOKUP and HLOOKUP Function. Index and Match are actually two separate functions. When we combine these two, it becomes a power pack function for looking up values from one data set to other.
Before learning the combined function, it is better to understand these two function separately.
INDEX Function
INDEX function returns the value or reference of the cell at the intersection of a particular row and column, in a given range.
The Index Function has three arguments.
Array: Array is a range of cells that has some data (A3:D8 in below image illustration).
Row_num: The number of row in the array from which you want to return the result (5 in below image).
Column_num: The number of column in the array from which you want to return the result (3 in below image).
After defining these parameters in the formula, INDEX function returns the value from the intersection point of the row and column number. See in below example for more clarity.
MATCH Function
MATCH function returns the relative position of an item in a range or array. It displays either of the row and column position of the lookup value.
MATCH function has three arguments.
lookup_value: This is the item that you want to search for or want to get the position of it.
lookup_array: This is the range where you have the value that you are looking for.
[match_type]: This is the type of match. Here you have three options; “less than”, “exact” and “greater than”.
In the example below, Match Function is returning the column position of “North” and row position of “Papaya” in the range B4:E4 and A5:A10.
INDEX and MATCH Combined Function
Let’s learn how we can use combination of Index and Match to perform looking up values from one data to another.
In the example above, we need to get data from table 1 to table 2. To look for the value in first cell of table 2 (I6), we need the value at intersection point of orange and south of the table 1, which gives by the INDEX function. And the MATCH function gives the row number for Orange and column number for South.
You can write a formula in I6 cell and copy the same formula for other cells. For this while writing the formula, use $ symbols the keep the cell references absolute if require.
Write this formula in the I6 cell;
=INDEX($B$5:$E$10,MATCH($H6,$A$5:$A$10,0),MATCH(I$5,$B$4:$E$4,0))
Copy the same formula for rest of other cells.
Why Index and Match is Powerful than VLOOKUP/HLOOKUP
Index and Match is more powerful than VLOOKUP/HLOOKUP because it overcomes the limitation of VLOOKUP/HLOOKUP that is the lookup value must be at the left column or top row. Further, in Index and Match, the row number and column number are dynamically captured by the MATCH function. Whatever the size of data, lookup can be performed easily by drag and drop the formula written in first cell.
Practice Workbook Download
If you want to practice Index and Match function, please download the excel workbook from link given below.
