INDEX and MATCH Function in Excel
Microsoft Excel, or Excel, is one of the widely used powerful spreadsheet programs that allows users to record large amounts of financial data within several cells across multiple sheets. It also has a wide range of built-in functions and formulas to help us perform financial analysis and complex calculations on the recorded data. INDEX and MATCH are two different built-in Excel functions typically used to perform advanced lookups.
This article discusses a brief introduction to the INDEX and MATCH functions. Also, it explains how we can use INDEX and MATCH functions together to perform complex lookups in Excel with the help of relevant examples.
What is the INDEX function in Excel?
The use of the INDEX function is mainly seen in advanced Excel formulas. In particular, the INDEX function helps us retrieve the value at a specified location in a range. In other words, the INDEX function typically returns the content of a cell at the intersection of a row and column that we specify. However, it is essential to note that the function only returns a specific value in a one-dimensional range.
Syntax of INDEX Function
The syntax of the INDEX function is defined as below:
Where reference, row, and column are the respective parameters or arguments of the INDEX function.
Note: If we do not specify both the row and column, the INDEX function will extract the entire reference range supplied within it.
Let us understand INDEX function applications with the help of examples for various use-cases. Consider the following excel sheet as an example dataset for all the cases of the INDEX function. In our example sheet, the first cell starts from cell B2 (Item) and ends at the last diagonal cell F9 (1797).
Case 1: When rows and columns are not specified
When inserting an INDEX function in cell G2 in our example sheet, the INDEX function returns the following result if we do not specify the optional parameters rows and columns, i.e., =INDEX(B2:C9):
Case 2: When a reference range and rows are specified
If we only specify a row along with the reference range but not the column in the INDEX function, we get the following results:
Case 3: When a reference range and columns are specified
If we only specify a column along with the reference range but not the row in the INDEX function, we get the following results:
Case 4: When all parameters are specified
If we specify all the parameters like row, column, and the reference range in the INDEX function, we get the following results:
The limitation with the INDEX function is that the function requires row and column values to locate any specific data from the reference table. That means, if we use the INDEX function to deal with the ML dataset of 10,000 or more rows and columns, it will be very difficult for us to specify any specific row and/ or column value in the INDEX function. It is where we will need to use the MATCH function that allows us to identify rows and columns based on certain rules or conditions.
What is the MATCH function in Excel?
Like the INDEX function, the application of the MATCH function is also mostly seen in advanced Excel functions. In particular, the MATCH function helps us retrieve the position of an item/ value at a specified location in a range. In other words, the function helps us find the lookup value position in the given array.
The MATCH function is considered a less refined form of Excel's VLOOKUP or HLOOKUP functions and only returns the location information without copying or extracting the actual data. It is case-insensitive and irrespective of the position of the range, be it vertical or horizontal.
Syntax of MATCH Function
The syntax of the MATCH function is defined as below:
Where the search_key, range, and search_type are the respective parameters or arguments of the MATCH function.
Let us understand MATCH function applications with the help of examples for various use-cases. Now, let's again consider the same sheet as an example data for all the cases of the MATCH function.
Case 1: When search type is zero (0), which means Exact Match
When inserting a MATCH function in cell G2 in our example sheet, the function returns the following result if we specify the optional parameter search_type as 0 (Exact Match), i.e., =MATCH("Tool",C2:C9,0):
Case 2: When search type is one (1), or Default
If we specify the search_type as 1, which is the default parameter value, we get the following results:
It is essential to note that the result will be the same if we don't specify anything for the search_type parameter. The reason for the same value is that the MATCH function automatically considers it as 1 by default.
Case 3: When the search type is a negative one (-1)
If we specify the search_type as -1, we get the following result:
We typically use the exact match in most cases, so we specify the search_type as 0. We usually find the row/ column number with the help of the MATCH function and then use the values within the INDEX function. That way, if the INDEX function finds the information (or value) regarding the supplied row/column number, the corresponding information is extracted in the resultant cell.
Combining INDEX and MATCH functions together
We have already learned the basics of INDEX and MATCH functions. However, it is more beneficial if we use both functions together by combining them within a single formula. When both the functions are combined together, they become the most popular Excel tool to perform advanced lookups. Because of its usefulness, many users even choose to use INDEX and MATCH functions combined instead of using the VLOOKUP.
Combining the INDEX and MATCH functions typically enables us to look at a range of data and extract the desired value at the intersection of a specific row and column. Nesting these two functions is so flexible and efficient that we can perform vertical and horizontal lookups, left lookups, two-way lookups, case-sensitive lookups, closest match, and even lookups as per custom-defined rules or conditions.
Excel's VLOOKUP function can only search for a value in the first column (left-most column) of data to provide the specific adjacent value. However, we can utilize the INDEX and MATCH functions together to navigate any column and retrieve a value in any row.
The syntax of the INDEX and MATCH functions combined can be defined as below:
Let us consider the same example sheet again and apply the combined formula on the INDEX and MATCH functions to find the desired value.
Suppose we want to find the cost of the Keyboard. It is visible that the costs of all the items are recorded in column D (which is column 3 in our range B2:F9). If there is a large data set, the position of a row will not be known. So, let's consider that the position of the row for the Keyboard is not known. In that case, we apply the combination formula of INDEX and MATCH in the following two steps:
Let us explore other examples of the INDEX and MATCH function together in different cases:
Two Way Lookup using INDEX and MATCH
In the previous example, the position of the column with costs was known. So, the process was not fully dynamic. Let us consider that we don't know the column position, and we need to find the cost of the item Keyboard. In that case, we have to use Two-Way Lookup using the INDEX and MATCH functions. When we find both the row and column values using the MATCH function (without supplying any static value) and supply it in the INDEX function to obtain a specific result, it is termed Two-Ways Lookup.
In our example, we have first to apply the MATCH formula in the following way:
The above formula will find the column number of costs. Here, B2:F2 represents headers.
Now, we supply the above formula in the INDEX function, making it a two-way lookup in the following way:
=INDEX(B2:F9,MATCH("Keyboard",B2:B9, 0),MATCH("Cost" ,B2:F2 ,0))
Left Lookup using INDEX and MATCH
Performing a 'left lookup' is one of the primary advantages of the INDEX and MATCH functions, which is not possible using the VLOOKUP. It is the ability to extract the position of a row for the desired item from the right and find the respective value from the left.
In simple terms, suppose we want to find an item to purchase that costs us 699 Rs. In our example data, we can see that we refer to a Keyboard. Here, the cost is known, and the item name must be found.
Since the cost column is placed on the right side of the item column, finding the desired result (item name) is not possible using the VLOOKUP. Let us now find the item name that costs 699 by using the INDEX-MATCH function:
Case-Sensitive Lookup using INDEX and MATCH
By default, Excel's MATCH function is case-insensitive. It means when we use the MATCH function to get the row position of an item Keyboard but supply the term as 'KEYBOARD', 'keyboard' or 'KeyBoard', the function will return the same results. Therefore, we must utilize the EXACT function when using the MATCH function in the combination of the INDEX function to perform the case-sensitive lookup, respecting the upper and lower cases.
The EXACT function is typically used to compare two different strings, considering their match cases (upper and lower character cases), and returns TRUE if they are the same exactly. If they don't exactly match, the EXACT function returns FALSE. That's because the EXACT function is case-sensitive.
Suppose we want to find the category of our desired item 'Keyboard' but in a strictly Case-sensitive way.
We can use the following combination of INDEX-MATCH and EXACT function:
=INDEX(C2:C9,MATCH(TRUE,EXACT("Keyboard", B2:B9) ,0))
In the above formula, the MATCH function returns TRUE only if it finds the exact value 'Keyboard' in the defined range B2:B9; otherwise, it returns FALSE. After that, the MATCH function will search in a range B2:B9 and find the row position of the value that returns TRUE. Lastly, the INDEX function will extract the respective value (item name) in a range C2:C9 at the located row of the MATCH function. That is how the INDEX-MATCH lookup works in a case-sensitive way.
Apart from this, we try to find the category of an item 'Keyboard' using the upper case characters (KEYBOARD); the formula will return #N/A! error because no TRUE value will be found in the MATCH function evaluation.
Multiple Criteria Lookup using INDEX and MATCH
It is a bit tricky to perform a lookup using multiple criteria in Excel. In such cases, we have to lookup for values that get matched on multiple columns simultaneously. We can utilize the INDEX-MATCH function in combination with the Boolean logic to find matches on more than one column simultaneously.
Suppose we want to find out the total cost of an Item 'Keyboard' where its individual cost is '699' and the total number of quantities is '6'. So, we have to perform a Match for three different criteria, such as an item, individual cost, and quantity. We can do this using the steps below:
Advantages of INDEX-MATCH over VLOOKUP
Next TopicWhat is concatenate in Excel