INDEX and MATCH Function in ExcelMicrosoft 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 builtin functions and formulas to help us perform financial analysis and complex calculations on the recorded data. INDEX and MATCH are two different builtin 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 onedimensional range. Syntax of INDEX FunctionThe syntax of the INDEX function is defined as below: Where reference, row, and column are the respective parameters or arguments of the INDEX function. Parameters/ Arguments
Note: If we do not specify both the row and column, the INDEX function will extract the entire reference range supplied within it.ExamplesLet us understand INDEX function applications with the help of examples for various usecases. 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 caseinsensitive and irrespective of the position of the range, be it vertical or horizontal. Syntax of MATCH FunctionThe 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. Parameters/ Arguments
ExamplesLet us understand MATCH function applications with the help of examples for various usecases. 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 togetherWe 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, twoway lookups, casesensitive lookups, closest match, and even lookups as per customdefined rules or conditions. Excel's VLOOKUP function can only search for a value in the first column (leftmost 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. SyntaxThe syntax of the INDEX and MATCH functions combined can be defined as below: ExamplesLet 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 TwoWay 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 TwoWays Lookup. In our example, we have first to apply the MATCH formula in the following way: =MATCH("Cost",B2:F2,0) 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 twoway 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 INDEXMATCH function:
CaseSensitive Lookup using INDEX and MATCH By default, Excel's MATCH function is caseinsensitive. 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 casesensitive 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 casesensitive. Suppose we want to find the category of our desired item 'Keyboard' but in a strictly Casesensitive way. We can use the following combination of INDEXMATCH 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 INDEXMATCH lookup works in a casesensitive 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 INDEXMATCH 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 INDEXMATCH over VLOOKUP
Next TopicWhat is concatenate in Excel
