# 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

**are the same. Otherwise, the**

*text2***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.