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.

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.

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.

Step 2:

• Press Enter to the result.

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

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.

Step 1:

• Type the following formula in cell G5.

Step 2:

• Then, press Enter to see the result.

Step 3:

• Finally, use AutoFill to see results in full.

Read More: How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results

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.

Step 1:

• We want to find the ID of MARK instead of Mark. Let’s see what happens. Enter the following formula cell C5.

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.

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.

Step 1:

• Firstly, type the following formula in cell G5.

• Secondly, press Enter to get the first value in cell G5.

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

Step 2:

• To get the second lookup value, type the following formula in cell G6.

• This is an array function so we need to apply this function by pressing Crtl + Shift + Enter

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

Read More: INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)

Similar Readings

• Excel INDEX MATCH to Return Multiple Values in One Cell
• Excel INDEX-MATCH Formula to Return Multiple Values Horizontally
• [Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)
• How to Select Specific Data in Excel (6 Methods)
• INDEX MATCH vs VLOOKUP Function (9 Examples)

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.

Step 1:

• Enter the formula in cell G7.

Step 2:

• Finally, press Enter.

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.

Step 1:

• To find the closest match, type the following formula.

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.

Step 2:

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

Read More: INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)

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.

Step 1:

• To perform Three-way Lookup, at first type the formula below in cell H6.

Step 2:

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

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.

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.

Step 1:

• Firstly, in cell G5, type the formula below.

Step 2:

• Then, press Enter button to see the changes.

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.

Read More: INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)

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.

Related Articles

• INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)
• Index Function to Match and Return Multiple Values Vertically in Excel
• INDEX MATCH across Multiple Sheets in Excel (With Alternative)
• Index Match Multiple Criteria in Rows and Columns in Excel
• INDEX MATCH Multiple Criteria in Excel (Without Array Formula)
• Excel Index Match single/multiple criteria with single/multiple results