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.

Parameters/ Arguments

• reference: It is a compulsory parameter used to specify the array of cells that need to be offset into. A reference can be specified as a single range or an entire dataset within a specific table.
• row: It is an optional parameter used to specify the number of offset rows. Using this parameter, we specify the vertical distance at which we want to extract the values in the specified range. Suppose we enter the reference range as ‘A1:A6’. In that case, we will have to use 1 for A1, 2 for A2, etc. If we enter row as 5, the function will extract from A5. If we do not specify any value for a row, the function will extract all the rows specified in the reference range, i.e., A1 to A6.
• column: It is another optional parameter used to specify the number of offset columns. Using this parameter, we specify the horizontal distance at which we want to extract the values in the specified range. Suppose we enter the reference range as ‘A1:B6’. In that case, for A1, the row and column both will be 1. However, for B1, the row will be 1, but the column will be 2. If we enter row as 5 and column as 2, the function will extract the value from B5. If we do not specify any value for a column, the function will extract all the columns specified in the reference range.

Note: If we do not specify both the row and column, the INDEX function will extract the entire reference range supplied within it.

Examples

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.

Parameters/ Arguments

• search_key: It is a required parameter for the MATCH function to specify the value to look for. For example, any specific name (Akash), item (Mouse), number (907), ID (JTP-001), etc.
• range: It is another required parameter for the MATCH function to specify the one-dimensional array we want to look for. It can be represented in either a single row or a single column. For example, A1:D1, A1:A9, C1:C10, etc.
• search_type: It is the search method that can be supplied in the MATCH function. It is an optional parameter. It typically uses the following three values: 1, 0. -1.
  • When search_type =1: It is the default search value used by the MATCH function if we don’t supply the desired one. In this case, the function finds the largest value less than or equal to search_key when the defined range is sorted as ascending. It usually accepts a near-match, rounded down to the next available value.
  • When search_type =0: In this case, the function finds the exact value while the defined range is sorted in ascending or descending order. If the function finds no exact match, it returns an error.
  • When search_type=-1: In this case, the function finds the smallest value greater than or equal to search_key when the defined range is sorted as descending. It usually accepts a near-match, rounded up to the next available value.

Examples

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.

Syntax

The syntax of the INDEX and MATCH functions combined can be defined as below:

Examples

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:

• First, we need to find the position of the item Keyboard by using the MATCH function in the following way:
  =MATCH(“Keyboard”,B2:B9,0)
  Here, the range B2:B9 represents the column that contains a list of items, and the search type is set as 0 to find the exact match. This way, we find the exact row number or position of the item Keyboard.
• Next, we need to find the cost of the item Keyboard. For this, we need to apply the INDEX function for the entire range and combine the MATCH function query within the INDEX function. The column number where costs are recoded is already known. Therefore, we apply the combined formula in a cell (I3) where we want to calculate the cost of the Keyboard in the following way:
  =INDEX(B2:F9, MATCH(“Keyboard”,B2:B9,0),3)
  

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:

=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 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:

• First, we need to apply the following formula to extract the row position that consists of our desired cost value (i.e., 699):
  =MATCH(699,D2:D9,0)
  Where D2:D9 represents the range in a cost column where the formula will look for the desired cost.
• Once the row number is located, we need to apply the INDEX function in a resultant cell (I2) to extract the respective item name. Thus, we combine the MATCH formula within the INDEX function in the following way:
  =INDEX(B2:B9,MATCH(699,D2:D9,0))
  Where B2:B9 represents the range in an item column where the formula will look for the respective item (i.e., Keyboard).
  

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:

• First, we need to match an Item column (B2:B9) with ‘Keyboard’ using the below condition:
  “Keyboard”=B2:B9
  This way, we convert the range B2:B9 (i.e., our Item column) values into Boolean. That returns TRUE when an item is ‘Keyboard’, otherwise FALSE.
• Similarly, we need to match the Cost criteria using the below condition:
  699=D2:D9
  This way, we convert the range D2:D9 (i.e., our Cost column) values into Boolean. That returns TRUE when the cost is 699; otherwise, FALSE.
• Next, we need to match the third criteria, ‘Quantity’, using the below condition:
  6=E2:E9
  This way, we convert the range E2:E9 (which represents the quantities) values into Boolean. It returns TRUE when the quantity will be 6; otherwise, FALSE.
• After that, we have to multiply the results of all three criteria. It will be the intersection of all desired conditions and transform Boolean TRUE and False into 1 and 0.
• After converting the Boolean TRUE/FALSE into 1/0, the resultant value will be a column with either 0 or 1. Now, we need to apply the MATCH function to locate the row position of the respective columns containing values as 1. If the column has a value of 1, it satisfies the first, second, and third criteria of our example data.
• Once we extract the row position, we supply it in the INDEX function to find the total cost of the corresponding row in the following way:
  =INDEX(F2:F9,MATCH(1,(“Keyboard”=B2:B9)*(699=D2:D9)*(6=E2:E9),0))
  Where a range F2:F9 represents the column consisting of the total costs of items.
  

Advantages of INDEX-MATCH over VLOOKUP

• INDEX-MATCH is faster and easier to use.
• INDEX-MATCH can work for vertical as well as horizontal ranges.
• INDEX-MATCH can work for both left and right lookups.
• INDEX-MATCH can work with ascending and descending both the data arrangements.
• INDEX-MATCH is independent of the actual position of the column.

---