Examples with INDEX-MATCH Formula in Excel (8 Approaches)

For advanced lookups in Excel, the INDEX and the MATCH can be used instead of VLOOKUP. INDEX and MATCH are required for extensive lookups and a lot of them are absolutely amazing. With example, we’ll teach you how to utilize the Excel INDEX and MATCH functions in Excel in this tutorial.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


8 Effective Examples of the INDEX-MATCH Formula in Excel

We’ll use an example data set in the sections below to demonstrate how to utilize the INDEX and the MATCH functions on it. We’ll show you how to apply both functions in 8 different ways to do a lookup for values.

♣Syntax:

=INDEX (array, row_num, [column_num])

♣Arguments:

  • array: the range of cells of values
  • Row_num: returns results for a row in the selected range.
  • column_num: returns results for a column in the selected range.

♣Result:

The Index Function returns the value or reference of the cell in a specified range that is at the intersection of a specific row and column.

♣Syntax:

=MATCH(lookup_value,lookup_array,[match_type])

♣Arguments:

  • lookup_value: The value you want to find in range.
  • lookup_array: The range where you are searching the lookup_value.
  • match_type: Specifies how lookup value and lookup array values are matched in Excel.
    • 1 = exact or next smallest.
    • 0 = exact match.
    • -1 = exact or next largest.

♣Result:

Returns the position of an item in an array in terms of its relative position.

Basic Combination of the INDEX and the MATCH Function in Excel

Example 1: Basic Combination of the INDEX and MATCH Functions in Excel

Let’s say, you want to do a lookup the Price for a particular Order ID. We’ll combine the INDEX and the MATCH function to do so.

Step 1:

  • In cell G5, type the following formula.
=INDEX(D5:D11, MATCH(G4,B5:B11,0))

Here,

  • MATCH(G4,B5:B11,0) refers to cell G4 as the lookup_value in the range B5:B11 for an exact match. It returns 4 as the value is in row number 4.
  • INDEX(D5:D11, MATCH(G4,B5:B11,0)) refers D5:D11 as an array from where we get the value and row_num is 4 as we got it from the MATCH Column_num is not required as we apply INDEX for only one column.

Basic Combination of the INDEX and the MATCH Function in Excel

Step 2:

  • Press Enter to the result.

Basic Combination of the INDEX and the MATCH Function in Excel


Example 2: Use the INDEX-MATCH Formula to Lookup from the Left in Excel

You may also do a lookup from one column to the other. The value for column C, the second column of our INDEX range B5:D11, will be found in the following example. Follow the steps below to perform a lookup from the left.

Use the INDEX and the MATCH Function to Left Lookup in Excel

Step 1:

  • Type the following formula in cell G5.
=INDEX($B$5:$D$11,MATCH(F5,$B$5:$B$11,0),2)

Use the INDEX and the MATCH Function to Left Lookup in Excel

Step 2:

  • Then, press Enter to see the result.

Use the INDEX and the MATCH Function to Left Lookup in Excel

Step 3:

  • Finally, use AutoFill to see results in full.

Use the INDEX and the MATCH Function to Left Lookup in Excel


Example 3: Analyze Case-sensitive Condition by Combining INDEX and MATCH Functions in Excel

For Case-sensitive analysis, you can apply the INDEX and the MATCH function in combination.

To analyze and do a lookup, follow the steps below.

Analyze Case-sensitive in Excel by applying the INDEX and the MATCH Function

Step 1:

  • We want to find the ID of MARK instead of Mark. Let’s see what happens. Enter the following formula cell C5.
=INDEX(B5:B11,MATCH(TRUE,EXACT(G4,C5:C11),0))

Analyze Case-sensitive in Excel by applying the INDEX and the MATCH Function

Step 2:

  • Finally, press Enter to find the Order ID for MARK.

Look closely; the result for the perfect match with the capital letter MARK was obtained, but not for the Mark.

Analyze Case-sensitive in Excel by applying the INDEX and the MATCH Function


Example 4: Lookup Two Columns with INDEX-MATCH Formula in Excel

It’s fascinating to note that you may do a lookup in multiple columns for multiple criteria at the same time. For example, we’ll look for a person’s Order ID as well as the Price. Follow the steps below to perform a lookup for two criteria.

Lookup Two Columns in Excel by the INDEX and MATCH the Function

Step 1:

  • Firstly, type the following formula in cell G5.
=INDEX(B5:B11,MATCH(TRUE,EXACT(G4,C5:C11),0))
  • Secondly, press Enter to get the first value in cell G5.

Lookup Two Columns in Excel by the INDEX and MATCH the Function

Therefore, you will the first lookup value in cell G5.

Lookup Two Columns in Excel by the INDEX and MATCH the Function

Step 2:

  • To get the second lookup value, type the following formula in cell G6.
=INDEX(D5:D11,MATCH(G4&G5,C5:C11&B5:B11,0))
  • This is an array function so we need to apply this function by pressing Crtl + Shift + Enter

Lookup Two Columns in Excel by the INDEX and MATCH the Function

  • As a result, you will have the first lookup value in cell G6.

Lookup Two Columns in Excel by the INDEX and MATCH the Function


Example 5: Two-Way Lookup Using INDEX-MATCH Formula

You can execute a lookup for a row and a column in Two-way. Follow the steps below to find a value in a row and column at the same time.

Lookup in Two-ways in Excel Using the INDEX and the MATCH Function

Step 1:

  • Enter the formula in cell G7.
=INDEX(B4:D11,MATCH(G5,B4:B11,0),MATCH(G4,B4:D4,0))

Lookup in Two-ways in Excel Using the INDEX and the MATCH Function

Step 2:

  • Finally, press Enter.

Lookup in Two-ways in Excel Using the INDEX and the MATCH Function


Example 6: Find the Closest Match in Excel by Merging INDEX and MATCH Functions

The INDEX function and the MATCH function are useful for finding or seeking the closest match between ranges. For example, we would want to determine who has the closest value to our target value, which is $15 in this case. To do so, simply follow the steps below.

Find the Closest Match in Excel by Applying the INDEX and the MATCH Function

Step 1:

  • To find the closest match, type the following formula.
=INDEX(C5:C11,MATCH(MIN(ABS(D5:D11-G4)),ABS(D5:D11-G4),0))

Here,

ABS(D5:D11-G4) is the absolute value of difference with target value 15 between the range D5:D11.

MIN(ABS(D5:D11-G4) commands to find the minimum difference of the absolute values.

Find the Closest Match in Excel by Applying the INDEX and the MATCH Function

Step 2:

  • By pressing Ctrl + Shift + Enter, we will apply this function as an array function.

Find the Closest Match in Excel by Applying the INDEX and the MATCH Function


Example 7: Three-Way Lookup Using the INDEX and MATCH Functions in Excel

A Three-way Lookup is the finest thing you can accomplish using the INDEX and the MATCH function. Three-way Lookup adds an additional dimension of relevance. For example, we can figure out what Jenny paid for two different items in three separate months. Follow the easy steps outlined below to get it done.

Three-Way Lookup in Excel

Step 1:

  • To perform Three-way Lookup, at first type the formula below in cell H6.
=INDEX(($D$5:$E$7,$D$10:$E$12,$D$15:$E$17),MATCH($G$6,$C$5:$C$7,0),MATCH(H$5,$D$4:$E$4,0),(IF(H$4="Jan",1,IF(H$4="Feb",2,3))))

Three-Way Lookup in Excel

Step 2:

  • Finally, press Enter to get the first lookup value.

Three-Way Lookup in Excel

Step 3:

  • Then, copy the formula for other cells by using the AutoFill

Therefore, you will obtain the results as shown in the below image.

Three-Way Lookup in Excel


Example 8: Apply the INDEX-MATCH Formula with Wildcard Characters

Excel can also determine the partial match value. The INDEX and MATCH functions are used to do this. To accomplish so, we’ll use an asterisk (*) as a wildcard character. Simply follow the instructions outlined below to get started.

Wildcard Characters

Step 1:

  • Firstly, in cell G5, type the formula below.
=INDEX($D$5:$D$11,MATCH(F5&"*",$C$5:$C$11,0),1)

Wildcard Characters

Step 2:

  • Then, press Enter button to see the changes.

Wildcard Characters

Step 3:

  • Just use the AutoFill tool to fill the required cells.

As a consequence, you can see that it returns a result for “Jenny,” even though it isn’t exactly matched.

Wildcard Characters


Conclusion

To sum up, I hope this article has demonstrated how to utilize the INDEX and MATCH functions to integrate multiple criteria. Look over the practice book and put what you’ve learned to use. We are willing to reimburse programs like this because of your support.

If you have any questions, please do not hesitate to contact us. Please let me know what you think in the comments section below.

Your questions will be answered as soon as possible by the ExcelDemy professionals.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo