MATCH INDEX is one of the most helpful and highly used functions to extract the required data from the dataset. These are actually two separate excel functions applied together-
MATCH Function looks for a specific value in a range of cells and returns the position of that value in that selected range. This is almost similar to the VLOOKUP Function but the only difference is that the MATCH function returns the position of that value in the selected range while the VLOOKUP function returns the corresponding value to that lookup value.
In short, if the selected range is A2:A4 which contains ICICIBANK, AXISBANK, and HDFCBANK, then the formula =MATCH("HDFCBANK",A2:A4,0) returns the number 3 because HDFCBANK is the third item in the selected range.
INDEX Function index the values in the selected range/dataset and returns the corresponding value using user-defined row number and column number from the selected range/dataset (not excel defined column or row number).
Combining both MATCH and INDEX Function vertically and horizontally looks for a particular value that the user wants to look for in the dataset and populates the user required value corresponding on the basis of column number and row number.
Below we have explained the MATCH INDEX function with an example of how we can use the MATCH and INDEX formula to extract the required information from a huge dataset.
How to use the MATCH INDEX Function in Excel
We have stock price data such as Stock Name, Closing Price, Price Change between 2 days, 52 Weeks Low and 52 Weeks High prices for 50 stocks under NIFTY 50.
Now suppose out of the 50 stocks we want to find out the stock price details for HDFCBANK. Without using any formula/function it might be a difficult and long manual process and almost nearly impossible if the data set goes in lacs of rows. Of course, we can use function "VLOOKUP" in this situation and get the required data but what if the rows and columns are not in the same order of dataset table as shown below-
This makes it very difficult for a VLOOKUP function to perform because as we copy and paste or drag the formula to the right, the resultant values will be in accordance with the dataset column order and not the actually required column order.
Here is where the MATCH INDEX comes in and asks the user to define the required row and column value to look for in the indexed dataset table. In cell G3, we have a stock name "HDFCBANK" and we want to populate the other information such as Price Change, Closing Price, 52 Week Low, and 52 Week High.
Initially applying the INDEX formula in cell H3 to extract the price change of HDFCBANK from the dataset.
=INDEX(array, row_num, column_num)
array = the range of the dataset from which you want to extract the data. [ in our example, the array is cell A2 to E52 ]
row_num = the row number of the dataset in which the data you want to extract is present. [ in our example, row_num is HDFCBANK which is present in the nineteenth (19) row of the dataset ]
column_num = the column number of the dataset in which the data you want to extract is present. [ in our example, column_num is price change which is present in the third (3) column of the dataset ]
Obviously, identifying row and column numbers is again a manual process and time-consuming. Therefore, we use two MATCH formulas in the same cell (H3) to define the row in-place while defining row_num [ 19 ] and column in-place while defining column_num [ 3 ], letting excel identify the referenced cell values.
=MATCH(lookup_value, lookup_array, match_type)
lookup_value = the value for which you want to extract the data from the dataset. [ in our example, look_value is "HDFCBANK" and "Price Change" ]
lookup_array = the range of the dataset row/column in which you want to look at the value. [ in our example, lookup_array is range A2 to A52 / A2 to E2 respectively ]
match_type = either you want to search the lookup_value (HDFCBANK) with the exact match or approximate match. For an exact match, specify 0 or FALSE in the formula and for the approximate match, specify 1 or TRUE in the formula - If the user specifies 1 for match_preference, then it may extract the data of HDFC first.
Finally, the formula for MATCH INDEX can be imagined like-
=INDEX(dataset range, MATCH(HDFCBANK, A2 to E52, 0), MATCH(Price Change, A2 to E2, 0))
=INDEX(array, MATCH(lookup_value, lookup_array, match_type), MATCH(lookup_value, lookup_array, match_type))
Basic Rule to fix the Cell/Range in Excel
Whenever we want to copy and paste the formula to other cells (formulas that contain a reference to another cell/range), one thing that has to be kept in mind is that- whether we want to move the reference cell or range contained in the formula to other cell/range. If not, then we have to fix the cells so as to copy and paste the formulas to other cells. Basic Rule for Absolute and Relative Cell Reference in Excel are-
To fix the column - put the Dollar ($) sign before the Column Alphabet. [ $G3 ]
To fix the row - put the Dollar ($) sign before the Row Number. [ G$3 ]
To fix both column and row - put the Dollar ($) sign before both Column Alphabet & Row Number. [ $G$3 ]
In our example, formula for MATCH INDEX,
=INDEX($A$2:$E$52,MATCH($G3,$A$2:$A$52,0),MATCH(H$2,$A$2:$E$2,0)) and then press Enter.
In this way, we can fix the reference cell/range of cells within the formula and such formula can easily be copied and paste special (formulas) to other cells instead of manually writing the formula for each cell. Also, this MATCH INDEX function eliminates the limitations of the VLOOKUP Function i.e., rigidness in defining the column number which needs to be identified manually while MATCH INDEX is more dynamic.
Key Aspects while using MATCH INDEX Function
Reference Index Range
The range used for the array should start from the column in which the lookup_value is present. [ in our case, HDFCBANK is present in column A ]
Excel range always assigns the first row, the first column of the array as 1,1 known as indexing which can be used for reference. So it makes it easy for the user to use these references in the functions/formulas. [ as we used the MATCH formula which does the same thing in the backend and results in the row/column reference number ]
Excel always refer to match at first instance
If the first column of the table_array contains duplicate data, it shall refer to the value which matches lookup_value in the first instance.
Excel performs a case-insensitive lookup
Irrespective of the upper case or lower case value, excel lookup for the value.
#N/A Error in Excel
Multiple MATCH criteria for row/column in Excel
Multiple lookup_value can be defined in a single MATCH formula in case were multiple criteria are involved using Multiple MATCH.