How to Use INDEX MATCH Function for Row and Column Operation in Excel- 10 Examples

When performing an advanced lookup, the INDEX and MATCH functions can help you get the results you need quickly. VLOOKUP and XLOOKUP can also be used for the same purpose, but a combination of the INDEX and MATCH functions can yield more comprehensive results.

After reading this article with examples, you will be an expert on using the INDEX and MATCH functions. Besides, you will find many reasons to use the INDEX and MATCH functions instead of the VLOOKUP function.

The INDEX Function

The INDEX function retrieves the asked value from the given range or location in the Excel sheet. Excel formulas, particularly complex formulas, may produce the same result. However, comparatively, the INDEX function is incredibly powerful and easy.  

Syntax of the INDEX Function

=INDEX (array, row_num, [column_num])

Argument of the INDEX Function

array: The range of selected cells containing values

row_num: retrieves the result for a row from the selected range or array

column_num: retrieves the result for a column from the selected range or array

Note: You may use only ‘row_num’ or only ‘column_num’ or both, according to your desired lookup.

Let’s move on to an example.

For illustration, you have provided a list of countries, and you are seeking the name of the fifth country “Brazil” with a formula. You may utilize the INDEX function as follows:

=INDEX(B3:B9,5)

• B3:B9 is the selected range of data (column named “countries” in the Excel sheet), and 5 is the row number.

Now, if you want to get the area of Brazil with the INDEX function, you can apply a row number as well as a column number in the syntax explained above.

=INDEX(B3:C9,5,2)

Explanation:

• B3:C9 is the selected range of data in an Excel sheet from which location you may look up; 5 is the row number, and 2 is the column number.

In a brief, the INDEX function returns the value of any specific location in a group of cells depending on the numeric position. You only need to apply for the row number if the range is one-dimensional. If the range is two-dimensional, the syntax must contain both the columns as well as rows numbers.

The MATCH Function:

In an Excel sheet, the MATCH function retrieves a specific item’s numerical position from a specified range or location. The MATCH function is not case-sensitive.

Syntax of the MATCH Function

=MATCH(lookup_value,lookup_array,[match_type])

Arguments of the MATCH Function

lookup_value: the name of the item whose numeric position you want to know.

lookup_array: The range or area in the Excel sheet from which you want to lookup.

match_type:  Please see the table below.

For illustration, we would like to utilize the MATCH function to find the position of the country “Brazil” in a list of countries.

=MATCH(B7,B3:B9,0) 

If the list of countries is given horizontally, you can also get the position of the country “Brazil” by using the MATCH function. Just select the row according to the data range.

=MATCH(F3,B3:H3,0)

10 Examples of the INDEX and MATCH Functions application

Now we should apply the basics of the INDEX & MATCH formula together. Let’s see an example of the INDEX and MATCH formulas combined in one formula.

Example #1: INDEX And MATCH Function Together Operation

Let’s create a formula that delivers the score in Finance score for a given student. According to what we learned earlier, the INDEX function can extract a value from a range of row data. To return the Number obtained in Finance for VIKRAM, for example, we can provide range B3:C11 with row 3 and column 2:

=INDEX(B3:C10,3,2)   // returns 70

However, instead of user defined numbers, we prefer a dynamic lookup.

Obviously, the MATCH function will satisfy our needs. Just replace the row number with a MATCH function.

=INDEX(B3:C10,MATCH(H4,B3:B10,0),2)

To summarize, the INDEX function requires a numeric location. We applied the MATCH function to find those positions by nesting the INDEX function.

Example #2: Two-Way Lookup with INDEX and MATCH Function

In the above section of the article, we made the column number dynamic by inserting the MATCH function into the INDEX function. Now it’s time to make our lookup formula fully dynamic. Just replace the row and column numbers in the INDEX formula with two MATCH formulas.

Let’s try another example: 

Now, we want to return the number in any subject for a particular student.

The MATCH function works for horizontal as well as vertical arrays or data ranges. We can create a formula that returns Finance’s position in a horizontal row of subject names( Finance, Math & English).

=MATCH(C2,C2:E2,0)      // returns 1

Now, replace the row number in the INDEX formula with the above formula.

=INDEX(C3:E10,MATCH(H3,B3:B10,0),MATCH(H4,C2:E2,0))

Explanation:

• The 1st MATCH formula retrieves 3 and Insert 3 in the INDEX formula as row number.
• The 2nd MATCH formula retrieves 1 and Insert 3 in the INDEX Formula as column number.

The formula we discussed above implies the non-dynamic INDEX formula given below:

=INDEX(C3:E10,3,1)   //returns 70

Example #3: Left Lookup Using INDEX and MATCH Function

Why should we use the INDEX and MATCH rather than the VLOOKUP? One of the main reasons is that by using the INDEX and MATCH formulas, you can perform a left Lookup.

Simply, we want to get the student’s name by ID. In the example below, just enter the ID of any student, and the function will retrieve the student’s name.

=INDEX(B3:B10,MATCH(I3,F3:F10,0))

Example #4: Case-Sensitive Lookup Using INDEX and MATCH Function

We already know that the MATCH function does not care about case.. But the EXACT function makes it possible for the MATCH function to be case-sensitive.

Syntax of the EXACT Function:

=EXACT(text1, text2, …)

Arguments of the EXACT Function:

The EXACT function returns TRUE if two strings, text1, and text2 are the same. Otherwise, the EXACT function returns FALSE.

Now, just put the following formula in the output cell to get your desired value and press Enter.

=INDEX(C3:C10,MATCH(TRUE,EXACT(F4,B3:B10),0))

Example #5: Closest Match by INDEX and MATCH Functions

In this example, we will demonstrate how to use the INDEX and MATCH functions to solve the problem of trying to find the closest match. To produce a lookup array within the MATCH function, we must combine the MIN and ABS functions.

=INDEX(B3:B10,MATCH(MIN(ABS(C3:C10-F4)),ABS(C3:C10-F4),0))

Explanation:

• MIN(ABS(C3:C10-F4) provides the minimum number from the ABS function’s results. And the return is 2.
• Where ABS(C3:C10-F4)  provides the absolute value of the difference between the target value 100 and the range C3:C10.
• And the MATCH function returns the positional number of the smallest difference. So, the MATCH function returns 8 and is inserted in the row number of the INDEX function.
• Then the INDEX function retrieves the student’s name who obtained the closest number to the target number.

This problem can be solved more easily by the XLOOKUP function. Keep in mind that XLOOKUP is available only for Office 365 and Excel version 2021 or later.

Example #6: Multiple Criteria Lookup by INDEX and MATCH Functions

We’ll now move on to more difficult problems. Let’s search for a quick fix for a lookup that matches multiple columns at once. In the following example, we will use the INDEX and the  MATCH functions, along with Boolean logic, to create a Multiple Criteria Lookup.

=INDEX(E3:E9,MATCH(1,(H4=B3:B9)*(H5=C3:C9)*(H6=D3:D9),0))

Explanation:

• H4=B3:B9 returns 1 only when one value from the range B3:B9 matches with H4. And similarly, H5=C3:C9 and H6=D3:D9 do.
• (H4=B3:B9)*(H5=C3:C9)*(H6=D3:D9) returns 1 only when all three parts of the Boolean logic are TRUE.
• MATCH(1, H4=B3:B9)*(H5=C3:C9)*(H6=D3:D9), 0) inserts the row number in the INDEX formula for which all three parts of the Boolean logic are TRUE.
• Then, INDEX function returns the associated value from the data range E3:E9, according to the row number inserted by the MATCH formula.

For better understanding, we can simplify the formula:

=INDEX(E3:E9,5)   //returns 60

We hope that you can create two columns Lookup similarly by deleting one part of the Boolean logic from the formula.

Example #7: Three-Way Lookup in Excel by INDEX and MATCH Function

Assume, different products have multiple sizes and different quantities kept in stores X & Y, now you want to return the quantity of product F which has size M, those are kept in Store Y.

=INDEX(B3:E9,MATCH(H4&H5,B3:B9&C3:C9,0),MATCH(H6,B2:E2,0))

Explanation:

• MATCH(H4&H5,B3:B9&C3:C9,0) Returns the row number for which H4 and H5 match with the one value from the range B3:B9 and C3:C9
• MATCH(H6,B2:E2,0) Returns the column number for which H6 matches with the one value from the range B2:E2
• Then, INDEX function finds the corresponding value from the data range B3:E9, according to the returned row and column number by MATCH function explained above.

Example #8: Apply the INDEX-MATCH Formulas with Wildcard Characters.

To determine the partial match value, the INDEX and MATCH functions can be used. As a wildcard character, we have to use an asterisk (*).

=INDEX(C3:C8,MATCH(F4&"*",B3:B8,0),1)

Example #9: INDEX and MATCH functions with MAX, MIN Function

The MAX and MIN functions can be used to find the straight maximum and minimum from a given data range. But if you want to get data from another cell associated with those average, max, and min values, you have to use the MAX and MIN functions with the INDEX and MATCH formulas.

INDEX MATCH with MAX

=INDEX(B3:B8, MATCH(MAX(C3:C8), C3:C8, 0))  

//returns the value from data range B3:B8 associated with the maximum value of data range C3:C8

INDEX MATCH with MIN

=INDEX(B3:B8, MATCH(MIN(C3:C8), C3:C8, 0))

//returns the value from data range B3:B8 associated with the minimum value of data range C3:C8

Example #10: INDEX MATCH WITH IFNA / IFERROR

We noticed that the INDEX and MATCH formulas in Excel cannot find a lookup value if a #N/A error occurs. We can replace the standard error message with any customized text message, Let’s see an example to use the INDEX and MATCH formula in the IFNA / IFERROR

=IFNA(INDEX(C2:C8, MATCH(F4,B3:B8,0)), "No match is found")

=IFERROR(INDEX(C3:C8, MATCH(F7,B3:B8,0)), "Oops, an error occurred, please check again!")

Some Reasons to Use the INDEX and MATCH Instead of the VLOOKUP Function

1.    LEFT LOOKUP: if the lookup data range stays on the left side of the criteria data range, VLOOKUP can not lookup right to left. From the example, we cleared that left lookup can be done easily by INDEX and MATCH combined approach.

2.    FREQUENT INSERT OR DELETE COLUMNS: To use VLOOKUP’s syntax, it’s mandatory to specify the index number of the column you want to extract the data from. If a column is added or deleted VLOOKUP Formulae get broken or provide incorrect results. But INDEX and MATCH require the return column range instead of the index number. As the result, we just update the data range while updating the data range in INDEX and MATCH formulas.

3.     NO LIMIT OF VALUE’S SIZE FOR LOOKUP: The VLOOKUP function can lookup for a value whose length is lower than 255 characters. Otherwise, it gives a #Value error as result. So, INDEX MATCH is only one solution when you have to deal with long strings.

4.     HIGHER PROCESSING SPEED: Considering processing speed there is no significant difference between INDEX and MATCH VS VLOOKUP functions. But when your worksheet contains hundreds or thousand of rows consequently hundreds or more formulas you will find INDEX AND MATCH formulas faster than the VLOOKUP formula.

Conclusion

It would be our pleasure if you found this article, “How to Use the INDEX and MATCH Functions in Excel- 10 Examples,” useful. If you have any questions, please let us know. Feel free to comment below.